excel - Run Macro When ComboBox is Clicked

Question

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

Answer 1

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: