Excel VBA How to enable or disable checkboxes based on certain conditions by using vlookup

Question

I was trying to code in VBA to enable or disable the checkboxes for the mobile plan based on the brand selected as shown in the image.

The table on the left acts as an indicator of the availability of the mobile plans for the respective brands as indicated by "Y" or "N". For example, when the user selected "Apple" from the dropdown list, he is only allowed to tick the Mobile Plan 1.

The attempted solution is based on only one criteria, which is for "Apple" in this case. How do I enhance the coding so that when the user selects "Samsung", the status of the checkboxes will change accordingly?

I planned to declare a variable as an Integer to act as an column indicator (for eg, Apple = 2, Samsung = 3, Nokia = 4) and pass this integer to the each function "CheckBox_Change" and use the VLOOKUP function, but I get an error message:

"procedure declaration does not match description of event or procedure having the same name"

while I was trying to do so.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$G$1" Then

CheckBox1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False

Call CheckBox1_Change
Call CheckBox2_Change
Call CheckBox3_Change    
End If

End Sub

Private Sub CheckBox1_Change()

If Range("B3").Value = "Y" Then
    CheckBox1.Enabled = True
Else
    CheckBox1.Enabled = False
    CheckBox1.Value = False
End If

End Sub

Private Sub CheckBox1_Click()

If CheckBox1.Value = False Then
    Range("H3").Value = ""
End If

End Sub
Private Sub CheckBox2_Change()

If Range("B4").Value = "Y" Then
    CheckBox2.Enabled = True
Else
    CheckBox2.Enabled = False
    CheckBox2.Value = False
End If

End Sub

Private Sub CheckBox3_Change()

If Range("B5").Value = "Y" Then
    CheckBox3.Enabled = True
Else
    CheckBox3.Enabled = False
    CheckBox3.Value = False
End If

End Sub

Answer 1

I'd go this way (explanations in comments):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$1" Then
        With Range("B2:D2").Find(Target.Value, , xlValues, xlWhole) ' reference the cell with the proper Brand
            CheckBox1.Enabled = .Offset(1).Value = "Y" 'set checkbox visibility to match referenced cell column corresponding value
            CheckBox2.Enabled = .Offset(2).Value = "Y" ' same as above
            CheckBox3.Enabled = .Offset(3).Value = "Y" ' same as above
        End With
    End If
End Sub

---

you could also loop between checkboxes "indexes" and avoid some code duplication:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$1" Then
        Dim i As Long
        With Range("B2:D2").Find(Target.Value, , xlValues, xlWhole) ' reference the cell with the proper Brand
            For i = 1 To 3 ' loop from 1 to 3 (number of checkboxes)
                Me.OLEObjects("CheckBox" & i).Enabled = .Offset(i).Value = "Y" 'set current checkbox visibility to match referenced cell column corresponding value 
            Next
        End With
    End If
End Sub

Answer 2

Basing on @QHarr's answer, you can reduce the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "G1" Then
        Me.CheckBox1.Enabled = (Target = "Apple")
        Me.CheckBox2.Enabled = (Target = "Samsung")
        Me.CheckBox3.Enabled = (Target = "Samsung")
    End If
End Sub

Answer 3

Try the following. It uses Select Case to avoid repeating sections of code and having multiple subs.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "G1" Then

        Select Case Target.Value
        Case "Apple"
            Me.CheckBox1.Enabled = True
            Me.CheckBox2.Enabled = False
            Me.CheckBox3.Enabled = False
        Case "Samsung"
            Me.CheckBox1.Enabled = False
            Me.CheckBox2.Enabled = True
            Me.CheckBox3.Enabled = True
        Case Else
            Me.CheckBox1.Enabled = False
            Me.CheckBox2.Enabled = False
            Me.CheckBox3.Enabled = False
        End Select
    End If
End Sub