OLEObject Class Module

**yay_excel** · 07-11-2013, 06:00 PM

Hello. I would like to create GotFocus and LostFocus events in a class module to apply to all ComboBoxes on a certain worksheet. If I use MSForms.ComboBox I do not have access to the GotFocus and LostFocus events. If I use OLEObject I have access to only the GotFocus and LostFocus events. The ComboBoxes have already been added to another class (that may be incorrect terminology) using this code:

Please Login or Register  to view this content.

Is it possible for me to use the same type of setup for the OLEObject class as well? I am having trouble after much experimentation.

Basically I need all the ComboBoxes on the worksheet to share the same KeyDown, GotFocus, and LostFocus code. What is the best way to achieve that?

Thank you.

**Leith Ross** · 07-11-2013, 06:32 PM

Hello yay_excel,

What you are referring to here is known as Sub-Classing. You can Sub_Class a Sub-Classed object. It can get confusing pretty fast. This wouldn't be an issue if VBA were an Object Oriented Programming (OOP) language. Instead, it is an Object Based Programming language. The big distinction being VBA does not support true inheritance.

**yay_excel** · 07-11-2013, 06:42 PM

Rewriting in Java is not feasible for me. :P

Are you able to point me in the right direction?

**Leith Ross** · 07-11-2013, 06:55 PM

Hello yay_excel,

I wasn't suggesting rewriting the code in another language. It was simply a statement about why it is more difficult to accomplish this in VBA than in other OOP languages.

Actually, I am not sure you can attach a OLEObject class to a predefined OLE object like a ComboBox using VBA. Do you really need to handle these events?

**yay_excel** · 07-11-2013, 07:01 PM

I have a list of comboboxes which are used to select an item from a database. From that selection, there are multiple VLOOKUP formulas which pull additional information about that item from the database. If I do not handle those events, all of those formulas calculate every time the user enters any character into the comboboxes. I do not want to calculate until the user has exited the combobox so as to speed it up. In the GotFocus and LostFocus events I want to change the calculation mode to manual and automatic respectively. Formerly I had separate event code for each combobox but I am currently refactoring and was hoping I could implement a class module to accomplish this.

...Maybe the best thing would be to redo the entire sheet as a userform?

**Leith Ross** · 07-11-2013, 07:05 PM

Hello yay_excel,

The ComboBox has two events that can help: AfterUpdate and Exit. AfterUpdate occurs after the user has made an entry. This is good event to use for validation of the entry. The Exit event is similar to LostFocus. This occurs when the user has click on another object and before that object takes the focus.

Seems to me, the AfterUpdate event would work for what you want to do.

**yay_excel** · 07-11-2013, 07:14 PM

I don't have those events in my class module... I have:

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

What have I done wrong?

**Leith Ross** · 07-11-2013, 07:21 PM

Hello yay_excel,

It isn't you, I was thinking about a MSForms ComboBox. The standard ComboBox doesn't have those events.

It looks like we have to create a generic OLE object class that your ComboBox class can access. I'm just not sure if it will be triggered by the GotFocus and LostFocus events.