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

问题描述

我需要帮助了解如何在我点击ComboBox时进行宏运行。例如，ComboBox从另一列中提取列表，如果该列更改，则当我第一次单击ComboBox时，我想要查看更新的列表。现在它是在我单击ComboBox之后运行宏，然后单击已经存在的选项。一旦我重新打开，我会看到我第一次用户点击时看到的更新列表。任何帮助是感谢，谢谢你这么多!!!!

  sub costcenterdup（）
'
 'costcenterdup宏
 Application.ScreenUpdating = False 
使用表格（美元）
 .Range（K9：K& .Cells（9，K）。 ）.Row）.Copyy 
目的地：=表格（LookUp）。范围（E2）
结束于
用表格（LookUp）
。 $ E2：E& .Cells（.Rows.Count，E）。End（xlUp）.Row）.RemoveDuplicates 
 Columns：= 1，Header：= xlNo 
 b 
 $ b使用Application.Worksheets（LookUp）
 .Range（E2：E5000）Sort Key1：=。Range（E2）
 End With 
 
范围（C5）。选择
 Application.ScreenUpdating = True 
 End Sub 
  

解决方案

您可以将两种类型的组合框添加到工作表中。

• 其中一个是表单组合框，可以从




• 另一个是 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屋！