0

I have some vba code that automatically creates a few ActiveX comboboxes. In order to handle their events I fill a global Collection with CComboEvent objects (custom class that I wrote, see below), one for every combobox. The CComboEvent objects should handle the events. While cbx_Change() in the code below works as expected, cbx_GotFocus() does not fire.

I feel like I'm overseeing something, can anyone please help?

Thank you

Option Explicit

Public WithEvents Cbx As MSForms.ComboBox

Private Sub Cbx_Change()
    ' TODO: Filter data that is shown in ListFillRange
    ' For now just show that the event fires:
    MsgBox Cbx.Value  ' This works as expected on every key stroke
End Sub

Private Sub Cbx_GotFocus()
    MsgBox "FOCUS!"  ' Never shown
    ' Open the dropdown list
    Cbx.ListFillRange = "A1:A11"
    Cbx.DropDown
End Sub
Community
  • 1
  • 1
pwhty
  • 7
  • 1
  • 5
  • Can you get the same event to fire outside of your class? Also [this post](http://www.ozgrid.com/forum/showthread.php?t=49836&p=253945#post253945) indicates you cannot focus on a `ComboBox` on a `Worksheet`. Might want to try `Activate` instead. Or provide more detail about where these are being created. – Byron Wall Jun 02 '15 at 15:00
  • If I create a new workbook, insert a ComboBox on it and double click the ComboBox, it creates the change event handler automatically. – pwhty Jun 05 '15 at 21:26
  • Thanks for the input @Byron. Yes, GotFocus works outside my class like this: create workbook, insert ComboBox, double click it. This creates the change event handler automatically: `Private Sub ComboBox1_Change()`. Similar to that I can create the GotFocus event handler. While this works when written simply as a worksheet function, it doesn't inside my class. `Activate` also didn't work, which is fine. From the link you posted I understand `Activate` would move focus to the ComboBox by vba code. I don't want that. I want to detect when the user enters the ComboBox himself and respond to that. – pwhty Jun 05 '15 at 21:45
  • Ah, now I see it. Unfortunately, it looks like we are not the first on the case to this one: http://stackoverflow.com/questions/10761973/accessing-oleobject-events-in-excel-vba-using-custom-class and http://stackoverflow.com/questions/6390289/how-to-assign-an-event-to-multiple-objects-with-excel-vba. I was able to get a very similar effect though using `MouseDown` instead of `GotFocus` which does not exist. – Byron Wall Jun 05 '15 at 22:04
  • Long time has passed, but still: thanks @byron-wall for pointing me in the right direction. Starting with the links you sent me, I was able to adjust the requirements a bit and build a workable solution. – pwhty Jan 26 '16 at 10:07

1 Answers1

0

To close this question properly: the reason Cbx_GotFocus() never fires is that it really isn't available to MSForms.ComboBox in this context. The available events can be checked like this:

  1. Open the left drop down list above the vba editor window and choose the class element you want to respond to events forChoose element

  2. Open the right drop down list above the vba editor window to see which events are avalable Choose event

This is a trick that you can use at different occasions to check for built in events/functions in vba. A shame that I didn't think of it right away. I finally ended up using a combination of Cbx_KeyUp() and Cbx_DropButtonClick() to respond to user interactions in that particular project.

pwhty
  • 7
  • 1
  • 5