0

I have a class module where I am extending the functionality of the MSForms ComboBox in my Excel VBA project. I added the combo box reference to my class:

Private WithEvents cBox As ComboBox

I added a bunch of event handlers for this combo box including Enter event:

Private Sub cBox_Enter()
  ...
End Sub

This was working fine and suddenly at one point my Enter event stopped firing. So I started investigating and found that it is no longer listed as one of the events in my event list for "cBox". In fact other events are also missing. I did not do any changes in References when this happened. The last change I did before this happened is to comment out some of the events that I was not using to accomplish my functionality. Commenting them and restoring them did not bring back the Events. All of the missing event handlers are now listed as functions under the class itself (Ex. cBox_Enter)

Current list has:

BeforeDragOver
BeforeDroporPaste
Change
Click
DblClick
DropButtonClick
Error
KeyDown
KeyPress
KeyUp
MouseDown
MouseMove
MouseUp

However the same ComboBox control on the UserForm still lists all the missing events:

AfterUpdate (*)
BeforeDragOver
BeforeDroporPaste
BeforeUpdate (*)
Change
Click
DblClick
DropButtonClick
Enter (*)
Error
Exit (*)
KeyDown
KeyPress
KeyUp
MouseDown
MouseMove
MouseUp

Events with (*) above are missing in the class but present in the UserForm lists. I also went to the Object Browser and looked at the ComboBox Object and in the class browser those events are also missing.

Has anyone seen this before and knows how to get those events back in the class module?

NOTE: I am using Excel 2003

Tomasz
  • 343
  • 2
  • 9
  • Assuming that you didn't rename anything, you might want to try adding a new control to the form, compiling, and then removing it. – Comintern Sep 08 '18 at 16:39
  • You are definitely on the right track. When I did that it worked. It made those events come back, but it did not survive saving the .xls and reloading it. After reloading back to square one Events again not recognized! – Tomasz Sep 09 '18 at 00:45
  • That's probably an indication that your workbook got corrupted in some way. Unfortunately, my best follow-up suggestion would be to rebuild everything in a new .xls file. – Comintern Sep 09 '18 at 14:19
  • After rebuild in new .xls from export files same result Events still missing. Either the corruption gets exported or I have a faulty MSForms library installed. How could I check that? Object Browser lists it as: `Library MSForms` `C:\windows\SysWow64\FM20.DLL` `Microsoft Forms 2.0 Object Library` – Tomasz Sep 10 '18 at 13:27
  • `FM20.DLL` FileVersion is: `12.0.6510.5004` Size: `1,193,832 bytes` – Tomasz Sep 10 '18 at 14:09
  • Afterupdate/BeforeUpdate/Exit/Enter are not exposed through WithEvents (so it cannot be it worked previously). These (and all other) events can however be 'catched' by the connecttoconnectionpoint api – EvR Oct 02 '18 at 11:09

1 Answers1

1

At the outset, do not have enough reputation to post as comment so posting as answer.

I observed that controls have less events and methods when used in worksheet than on user form, have tried this on empty sheet in 2003 and Office 365 on different systems.

Ganesh
  • 43
  • 5
  • I am using it in my Class Module, so maybe same applies for class modules, although I tried `Private WithEvents MyComboBox1 as MSForms.ComboBox` in the code for `UserForm` (`Forms` module) which I set to the `ComboBox1` that was dropped on the `UserForm` and the result is the same `MyComboBox1` has those 4 events missing `ComboBox1` has them all and they are both defined in the same module! – Tomasz Sep 16 '18 at 03:51
  • You indirectly sparked a though which led me one step forward. What I did is: `Private WithEvents cBox As MSForms.ComboBox` and `Private WithEvents oBox As MSForms.Control` and now those 4 missing events are available on the oBox. However when I `Set oBox = cBox` I get a `Run-time error '459': Object or class does not support the set of events`. – Tomasz Sep 16 '18 at 04:21