2

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
Ralph
  • 9,284
  • 4
  • 32
  • 42
JExcelL22
  • 21
  • 1
  • 3
  • Please include your code. – findwindow May 03 '16 at 15:12
  • Added the code, thank you! – JExcelL22 May 03 '16 at 15:32
  • I guess the sub is tied to the combobox? Your code has nothing to do with combobox. Can't help you if you don't provide code to _the_ problem on hand XD – findwindow May 03 '16 at 15:33
  • I don't have a code yet. I don't know how to do what I am trying to do so that is why I wanted help. Is it possible to do something to trigger a macro to run when you click the ComboBox so that when the list pops up its updated? – JExcelL22 May 03 '16 at 16:02
  • There is not such event "when combo box is activated or clicked on" for form controls. This is only available if you are using an ActiveX combo box. Here is an answer where I describe how you can review all the events that are available in Outlook (same applies to Excel, PowerPoint, etc.) http://stackoverflow.com/questions/36940417/how-can-i-tell-when-rules-have-finished-processing/36960469#36960469 I'd suggest that you move your combobox to a form (which could be modeless form). Then you can use the `ComboBox1_Enter` event or the `ComboBox1_Click` event. – Ralph May 03 '16 at 18:13

1 Answers1

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: Combo Box from the Forms toolbar in Excel

ib11
  • 2,530
  • 3
  • 22
  • 55
  • @Ralph -- I don't know why do you say that. I did test it. I put a combo box on the sheet from the Control Toolbox. Double-clicked on it, which opened the VBA, I changed the event handler to the `GotFocus` and added `MsgBox "Test."` Then I closed VBE, closed the Control Toolbox to go out of design mode and VOILA. – ib11 May 03 '16 at 17:52
  • I am not sure why I thought that the combo box must be a form control. Yet, I am reading through the post again and - you are right (my mistake) - there is no mentioning of the combo box that it should / must be a form control. So, if the OP is using ActiveX combo boxes then you are absolutely right and that's possible. Yet, please include in your post that it **must** be an ActiveX combo box (just to make that clear). – Ralph May 03 '16 at 17:57
  • I did, but what other type of combo box can you put on a sheet anyway? Ahh... I guess you mean that the OP _wanted_ it on a form? – ib11 May 03 '16 at 18:02
  • A form control combo box: http://stackoverflow.com/questions/15455179/what-is-the-difference-between-form-controls-and-activex-control-in-excel-20 – Ralph May 03 '16 at 18:04
  • I see, I did not try Office 2010. That's another datum, thank you. So I did clarify it in the answer. – ib11 May 03 '16 at 18:06
  • This does not apply to Office 2010 only. This is the same since Office XP and still is (in Office 2016). – Ralph May 03 '16 at 18:11
  • Office XP? You mean from 2002? I tried this specifically in an Excel 2003 and there is only one type of combo box. – ib11 May 03 '16 at 18:47
  • Comparison of ActiveX and Form Controls in Excel 2003 (just for you) http://excelexperts.com/Form-Control-vs-ActiveX-Control How to access form controls in Excel 2003: http://peltiertech.com/forms-controls-and-activex-controls-in-excel/ Actually, it is also mentioned in this article that form controls were part of Excel before ActiveX controls (in 1995 and before). For more information just do a simple Google search. – Ralph May 03 '16 at 18:54
  • Thank you. It turns out I only ever used the ActiveX LOL. But looking, I realized that the OP probably does not even need a macro for what he wants to achieve, so I elaborated my answer. – ib11 May 03 '16 at 19:21
  • 1
    That's why I upvoted your answer. With the ActiveX addition (and even more now) your answer is good and should get accepted. Good work and I would assume that you learned a lot along the way (very much in the spirit of SO). – Ralph May 03 '16 at 19:27
  • Ok so I am trying to follow! I feel like you guys have the answer but I am confused!! Can I used a normal ComboBox or does it need to be the ActiveX? For some reason it won't let me add an ActiveX box. I might need a little more help on the steps to get this done so that when they click the box it is the updated list. The tricky part of this is that technically when they click the box I want the list that it is pulling from to update... which is what my original macro is doing. – JExcelL22 May 03 '16 at 19:57
  • @JExcelL22 -- See the last paragraph. You should use the combo box from the Forms toolbar (did you find this?) This type of box updates itself **automatically** which is exactly what you want. – ib11 May 03 '16 at 20:06