Excel - Set values of Userform Checkboxes based on cell contents

Question

I'm developing a userform, one section of which contains three checkboxes referring to different parts of the world. Depending on the combination these enter a text value into cell C9.

I want to have the checkboxes reflect what is in the cell already when the user goes back into the userform. I've been able to do this for every other item in the userform (option buttons, textboxes, comboboxes), but my checkboxes don't respond at all, they are simply unchecked when the userform appears, regardless of C9's value.

The following code is in the userform_intialize module. Any ideas?

If wsM.Range("C9").Value = "EU-5" Then
        NABox.Value = False And EUBox.Value = True And RoWBox.Value = False
    ElseIf wsM.Range("C9").Value = "EU-5 & RoW" Then
        NABox.Value = False And EUBox.Value = True And RoWBox.Value = True
    ElseIf Sheets("Menu").Range("C9").Value = "NA & EU-5" Then
        NABox.Value = True And EUBox.Value = True And RoWBox.Value = False
    ElseIf wsM.Range("C9").Value = "North America" Then
        NABox.Value = True And EUBox.Value = False And RoWBox.Value = False
    ElseIf wsM.Range("C9").Value = "NA & RoW" Then
        NABox.Value = True And EUBox.Value = False And RoWBox.Value = True
    ElseIf wsM.Range("C9").Value = "Rest of World" Then
        NABox.Value = False And EUBox.Value = False And RoWBox.Value = True
    Else: NABox.Value = False And EUBox.Value = False And RoWBox.Value = False
End If

Thanks for any help.

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2016-03-17T11:43:24

Put the Me. keyword in front of the checkbox name.
May also be better to use a SELECT CASE statement instead of ElseIf.

NABox.Value = False And EUBox.Value = True And RoWBox.Value = False needs to be three separate commands. Either on separate rows, or split with a : (both examples in the code below).

Private Sub UserForm_Initialize()

    With Me
        Select Case wsm.Range("C9").Value
            Case "EU-5"
                NABox.Value = False
                EUBox.Value = True
                RoWBox.Value = False
            Case "EU-5 & RoW"
                NABox.Value = False : EUBox.Value = True
                RoWBox.Value = False
            Case "NA & EU-5"

            Case Else

        End Select
    End With

End Sub

Edit - I don't think you need to explicitly declare the False tickboxes - they're False by default when the form opens.

Excel - Set values of Userform Checkboxes based on cell contents

1 Answers