How to simplify Multiple ComboBox Change Event code

**Faridwahidi** · 10-21-2016, 11:30 AM

Hi All Expert,

I have one comboBox depend on another 4 comboBoxes. It would be short to create change event

Private Sub ComboBox27_Change()

    If Me.ComboBox27.Value <> Empty Then
         For i = 28 To 31
            With Me.Controls("ComboBox" & i)
                   .Clear
                   .Enabled = False
            End With
        Next i

    Else
        For i = 28 To 31
            With Me.Controls("ComboBox" & i)
                   .Enabled = True
                   .Clear
                   .List = wsData.Range("AA2:AA11").Value
            End With
        Next i
    End If
End Sub

However, each of 4 ComboBoxes also depend on a single comboBox as described above. Thus, I have to create 4 times change event to ensure it working fine. Is there any method to create only one change event instead of 4 as described below?

Private Sub ComboBox28_Change()
Private Sub ComboBox29_Change()
Private Sub ComboBox30_Change()
Private Sub ComboBox31_Change()


 '\\The same code use for all change event
   If Me.ComboBox28.Value <> Empty Or _
      Me.ComboBox29.Value <> Empty Or _
      Me.ComboBox30.Value <> Empty Or _
      Me.ComboBox31.Value <> Empty Then
      
        With Me.ComboBox27
               .Clear
               .Enabled = False
        End With
   Else
        With Me.ComboBox27
               .Enabled = True
               .Clear
               .List = wsData.Range("AA2:AA11").Value
        End With
   End If

**Tooley** · 10-21-2016, 11:34 AM

You can create a combobox class, or if not familiar with classes, create a "sub sAllComboBoxes_Change" with your required code, then each combobox should have it's own change event with 1 line that calls "sAllComboBoxes_Change"

**Faridwahidi** · 10-21-2016, 11:39 AM

Hi tooley,

I'm really not familiar on the step mentioned. can you show me the sample?

My ComboBox27 depend on ComboBox28 to 31 and vice versa. It means both set depend to each other.

**Tooley** · 10-21-2016, 12:09 PM

Sure, there's plenty of examples for class events online, one of which simple to follow is http://www.ozgrid.com/forum/showthread.php?t=154659.

Regarding dependencies, if your comboboxes are all named "ComboBox" plus numbered and in the same sequence they are dependent (eg when combobox1 is populated open combobox2....etc) then your code can be kept generic relatively simple (untested example is for another generic sub in the userform module and is not using the class method):

Standard ComboBox Event:

Private Sub ComboBox27_Change()
    Call sAllComboBoxes_Change(Me.Combobox27)
End Sub

Untested generic sub in the UserForm module (change to suit etc):

Private Sub sAllComboBoxes_Change(objComboBox As Control)
    
    Dim iComboBoxNumber As Integer
    Dim sComboBoxName As String
    Dim oControl As Control
    
    sComboBoxName = "ComboBox"
    iComboBoxNumber = Application.Replace(objComboBox.Name, sComboBoxName, "")
    
    Set oControl = Me.Controls(objComboBox.Name)
    
    With oControl
        'do stuff with combobox that called this sub
    End With
        
    If Not oControl = Nothing Then Set oControl = Nothing
    
    Set oControl = Me.Controls(sComboBoxName & iComboBoxNumber + 1)
    
    With oControl
        'do stuff with the next ComboBox
    End With
            
    If Not oControl = Nothing Then Set oControl = Nothing
    
End Sub

The above can be slimmed down further, but is hopefully easier to understand?