单击 ComboBox 时运行宏 [英] Run Macro When ComboBox is Clicked

问题描述

我需要帮助弄清楚如何在单击 ComboBox 时运行宏.例如，ComboBox 正在从另一列中拉入一个列表，如果该列发生变化，我想在第一次单击 ComboBox 时看到更新的列表.现在它在我单击 ComboBox 后运行宏，然后单击一个已经存在的选项.重新打开后，我会看到更新的列表，我想在用户第一次点击时看到.任何帮助表示赞赏，非常感谢！！！！

Sub costcenterdup()'' costcenterdup 宏Application.ScreenUpdating = False带床单(美元").Range("K9:K" & .Cells(9, "K").End(xlDown).Row).Copyy目的地:=表格(查找").范围(E2")结束于使用表格(查找").Range("$E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row).RemoveDuplicates列:=1，标题:=xlNo结束于使用 Application.Worksheets("LookUp").Range("E2:E5000").Sort Key1:=.Range("E2")结束于范围(C5").选择Application.ScreenUpdating = True结束子

解决方案

有两种类型的组合框可以添加到工作表中.

• 其中之一是表单组合框，可以从表单"工具栏访问.

• 另一个是 ActiveX 组合框，可以从 控件工具箱"工具栏访问.

这里有一个很好的解释(链接由@Ralph 提供):

I need help with figuring out how to make a macro run when I click the ComboBox. For example, the ComboBox is pulling in a list from another column, if that column changes, I want to see the updated list when I click the ComboBox for this first time. Right now it is running the macro after I click the ComboBox and then click on an option that already exists. Once I reopen, then I see the updated list I want to see the first time a user clicks. Any help is appreciated, thank you so much!!!!

Sub costcenterdup()
'
' costcenterdup Macro
Application.ScreenUpdating = False
With Sheets("Dollars")
.Range("K9:K" & .Cells(9, "K").End(xlDown).Row).Copyy
Destination:=Sheets("LookUp").Range("E2")
End With
With Sheets("LookUp")
.Range("$E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row).RemoveDuplicates
Columns:=1, Header:=xlNo
End With

With Application.Worksheets("LookUp")
.Range("E2:E5000").Sort Key1:=.Range("E2")
End With

Range("C5").Select
Application.ScreenUpdating = True
End Sub

解决方案

There are two types of combo boxes that you can add to a sheet.

• One of them is a form combo box, which can be accessed from the "Forms" toolbar.

• The other one is the ActiveX combo box, which can be accessed from the "Control Toolbox" toolbar.

A very good explanation of these is here (link is courtesy of @Ralph): http://peltiertech.com/forms-controls-and-activex-controls-in-excel/

To the first one you can only assign one macro when the combo box changes.

But to the ActiveX ComboBox you can assign several. If you add it to the GotFocus event then it will run the macro every time the box gets focus:

Private Sub ComboBox1_GotFocus()

    'Add code here

End Sub

But if I correctly understand your question, that you want to have the combo box have the data in it from a column – then you need the first version of combo box (the form one) and simply right-click on it, select "Format control..." and on the Control tab set the cells you want the data filled with. It will automatically update the combo box for you and you will always see the values from the cells. There is no need for a macro in this case. See the below image:

这篇关于单击 ComboBox 时运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！