ComboBox Situation in Excel VBA

Question

I have an Excel sheet that's a process checklist. The background is there are 3 Combo Boxes (form control) and within each Combo Box there's multiple options the user can select. Based on what the user selects as a combination between the 3 Combo Boxes it will hide/unhide specific rows in a section and their corresponding checkboxes. I'm trying to code this in VBA.

Between setting everything up these are the problems I'm running into with VBA:

1. There are 60 different ways the user can select options between the 3 Combo Boxes. Including the "Select Option" options (which will be used), the first ComboBox has 5 options, the second 4 options, and the last 3 options. The obvious I've been trying is If/Else logic, but copying and pasting 60 times over seems very redundant and leaves the sheet prone to coding errors that will take time to fix. Is there a better way to code this? Or in the way I'm trying to do this for each change, the If/Else logic is the best way?

2. When coding (I've pasted a short example of part of my code below this section with a few of the cases), I run into "Method and data member not found" or "Invalid use of Me" errors when debugging. How do I fix this?

3. How do I hide and unhide the checkboxes along with the rows be hid and unhidden so the formatting doesn't change and there are not stray checkboxes everywhere?

Sub ComboBox1_Change()

'Combo Box 1 is Asset Type, Combo Box 2 is AUS, Combo Box 3 is Transaction Type


'Select, Select, Select OR Refinance

If Me.ComboBox1.Value = "Select Asset Type" And Me.ComboBox2.Value = "Select AUS" And Me.ComboBox3.Value = "Select Transaction Type" Or Me.ComboBox3.Value = "Refinance" Then
    
    Worksheets("Assets Checklist").Rows("19:37" And "39").EntireRow.Hidden = True
    

'Select, Select, Purchase

ElseIf Me.ComboBox1.Value = "Select Asset Type" And Me.ComboBox2.Value = "Select AUS" And Me.ComboBox3.Value = "Purchase" Then

    Worksheets("Assets Checklist").Rows("39").EntireRow.Hidden = False
    Worksheets("Assets Checklist").Rows("19:37").EntireRow.Hidden = True
    
    
'Liquid, Select, Select
    
ElseIf Me.ComboBox1.Value = "Liquid" And Me.ComboBox2.Value = "Select AUS" And Me.ComboBox3.Value = "Select Transaction Type" Then

    Worksheets("Assets Checklist").Rows("31:34").EntireRow.Hidden = False
    Worksheets("Assets Checklist").Rows("19:30" And "35:37" And "39").EntireRow.Hidden = True

And the above code continues for each case.

Answer 1

'Each of the three Combo Boxes have al Linked Cell 'In an another cell concatenate the numeric values of the three Linced Cells 'e.g. in cell A1 have this: =A2 & A3 & A4 'where A2, A3, and A4 are the Linked cells of CBox1, CBox2 and CBox3 'In every ComboBox value change, call the Sub 'SixtyCases with parameter the value of cell A1

Private Sub SixtyCases(combination As String)
   Select Case combination
      Case "111"  'first combination
         'YOUR CODE HERE
      Case "112", "132"  ' OR
         'YOUR CODE HERE
      Case "113"
         'YOUR CODE HERE
         '...
         '...
      Case "543" 'last combination
         'YOUR CODE HERE
   End Select
End Sub

Answer 2

If you have 60 distinct cases then the most maintainable method might be to use a worksheet with columns for CB1/2/3 values and a column to hold visible and hidden rows (as range address like "A1:A10,A12"). Create a method which reads the values and looks up the matching row on the sheet, then sets the row visibility accordingly.

'this is called from each of the 3 combos
Sub ShowHideRows()
    Dim cb1, cb2, cb3, arr, r As Long, rngHide As String, rngShow As String
    
    cb1 = comboValue("combo1")
    cb2 = comboValue("combo2")
    cb3 = comboValue("combo3")
    Debug.Print cb1, cb2, cb3
    
    arr = Me.Range("choices").Value 'lookup table is a named range
                                    'better on a different sheet....
    For r = 1 To UBound(arr, 1)
        If arr(r, 1) = cb1 Then
            If arr(r, 2) = cb2 Then
                If arr(r, 3) = cb3 Then
                    rngShow = arr(r, 4)
                    rngHide = arr(r, 5)
                    Exit For 'stop checking
                End If
            End If
        End If
    Next r
    
    If rngHide <> "" Then Me.Range(rngHide).EntireRow.Hidden = True
    If rngShow <> "" Then Me.Range(rngShow).EntireRow.Hidden = False
End Sub

'read a Forms combo control value
Function comboValue(cbName As String)
    With Me.Shapes(cbName)
        comboValue = .ControlFormat.List(.ControlFormat.ListIndex)
    End With
End Function

Setup: