为一个子目录下的多个activex组合框运行相同的更改事件

Question

我正在使用多个组合框，我有4组具有相同更改事件的组合框，例如，

A组:Combobox1, Combobox5, Combobox9…

B组:Combobox2, Combobox6, Combobox10…

C组:Combobox3, Combobox7, Combobox11…

但是，我通过为每个事件编写一个单独的单击事件来实现。

'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

我不使用它作为用户表单。组的偏移量是相同的。我如何用相同的代码一起触发它们?我期待你的帮助，谢谢。

Answer 1

将重复的组代码放入带有组合框参数的函数中，并让组中的每个组合框在单击时调用该函数，将它们自己传递进去。例如:

'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