CODEWITHSUNDEEP
excelvbacombobox
Ömer Faruk iskender
Ömer Faruk iskender

Reputation: 61

Run same change event for multiple activex comboboxes under one sub

I'm working with multiple comboboxes, I have 4 groups of comboboxes with the same change events eg,

Grup A: Combobox1, Combobox5, Combobox9 ...

Grup B: Combobox2, Combobox6, Combobox10 ...

Grup C: Combobox3, Combobox7, Combobox11 ...

However, I do it by writing a separate click event for each one.

'Grup A
Private Sub ComboBox1_Click()
On Error Resume Next
If ComboBox1.TopLeftCell.Offset(0, 2).Value <> "" Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        ComboBox1.TopLeftCell.Offset(0, 2).ClearContents
        ComboBox1.TopLeftCell.Offset(0, 4).ClearContents
        ComboBox1.TopLeftCell.Offset(0, 6).ClearContents
        ComboBox1.TopLeftCell.Offset(0, 8).ClearContents
        ComboBox1.TopLeftCell.Offset(0, 11).ClearContents
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If
End Sub

'Grup B
Private Sub ComboBox2_Click()
On Error Resume Next
If ComboBox2.TopLeftCell.Offset(0, 2).Value <> "" Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        ComboBox2.TopLeftCell.Offset(0, 2).ClearContents
        ComboBox2.TopLeftCell.Offset(0, 4).ClearContents
        ComboBox2.TopLeftCell.Offset(0, 6).ClearContents
        ComboBox2.TopLeftCell.Offset(0, 9).ClearContents
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If
End Sub

I'm not using it as a userform. The offsets of the groups are the same. How can I trigger them together with the same code? I look forward to your help, thank you.

Upvotes: 1

Views: 128

Answers (1)

Jonny Henly
Jonny Henly

Reputation: 4213

Put the repetitive group code into a function with a combobox parameter and have each combobox in the group call that function on click, passing themselves in. For instance:

'Grup A click handler
Private Sub GrupAClicked(ByRef cbox As ComboBox)
On Error Resume Next
If cbox.TopLeftCell.Offset(0, 2).Value <> "" Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        cbox.TopLeftCell.Offset(0, 2).ClearContents
        cbox.TopLeftCell.Offset(0, 4).ClearContents
        cbox.TopLeftCell.Offset(0, 6).ClearContents
        cbox.TopLeftCell.Offset(0, 8).ClearContents
        cbox.TopLeftCell.Offset(0, 11).ClearContents
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If
End Sub

'ComboBox1 click
Private Sub ComboBox1_Click()
    GrupAClicked ComboBox1
End Sub

'ComboBox5 click
Private Sub ComboBox5_Click()
    GrupAClicked ComboBox5
End Sub

'ComboBox9 click
Private Sub ComboBox9_Click()
    GrupAClicked ComboBox9
End Sub

'Grup B click handler
Private Sub GrupBClicked(ByRef cbox As ComboBox)
On Error Resume Next
If cbox.TopLeftCell.Offset(0, 2).Value <> "" Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        cbox.TopLeftCell.Offset(0, 2).ClearContents
        cbox.TopLeftCell.Offset(0, 4).ClearContents
        cbox.TopLeftCell.Offset(0, 6).ClearContents
        cbox.TopLeftCell.Offset(0, 9).ClearContents
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If
End Sub

'ComboBox2 click
Private Sub ComboBox2_Click()
    GrupBClicked ComboBox2
End Sub

'ComboBox6 click
Private Sub ComboBox6_Click()
    GrupBClicked ComboBox6
End Sub

'ComboBox10 click
Private Sub ComboBox10_Click()
    GrupBClicked ComboBox10
End Sub

Upvotes: 2

Related Questions