Event Handlers: Getting a handle to the calling object

[AllSensibleNamesTaken]

Hello,
Building something in Excel using VBA.
Is there a way to access the element that fired the event within the event
handler?

for example:
Sub ComboBox1_Click()
'Currently I am doing the following
ComboBox1.doSomething(bla,bla)

'Am I doomed to have to get at this object by name, and hence have to
change
'the event handler code of all the different combo boxes in the
'sheet in order to match their name?
'Ideally there would be a "this" keyword to use, but I don't think VBA
has that.
'Any other way

End Sub


Thanks so much

 

[Nigel]

Create a Class Module

 

[Bob Phillips]

Nice explanation!

OP, you can tie all of the controls to an event handling class, and from
there you can either get them all performing the same action, or test which
invoked the event and take appropriate action.

Are these forms combos or worksheet?

 

[AllSensibleNamesTaken]

Hi Bob, they are worksheet combos in my case

The thing is that writing a case or if block to test which combo box calls
is the same amount of work (albeit better organised) and causes a similar
maintenance burden than what I am doing now which is including the object's
name in each event habdler.

What I'd ideally lie is to write is something like:

Sub ComboBox1_Click()
doSomething(this.value)
end Sub

So Nigel, going with your suggestion of writing a class I'm not familiar
enough with VBA to know if VBA classes have a "this" pointer or something
similar I could use. Do they?

I also don't think it is possible (I certainly don't know how) to implicitly
inherit all the ComboBox behaviour in a user defined class. I know I could
try to write a Combo Box wrapper but can't bear the pain and maintenance
burden of explicitly overriding EVERY single Combo Box method and property)

Perhaps I misunderstood you?

 

[Jim Cone]

Re:...
What I'd ideally lie is to write is something like:
Sub ComboBox1_Click()
doSomething(this.value)
end Sub
'--

Then...
Sub ComboBox1_Click()
doSomething(Me.ComboBox1.Value)
End Sub

 

[Tim Williams]

Tried this?

http://www.j-walk.com/ss/excel/tips/tip44.htm

Tim

 

[AllSensibleNamesTaken]

Nice, I didn't know about "Me" in VBA.
But the equivalent of what I wrote wold be

Sub ComboBox1_Click()
doSomething(Me.Value)
End Sub

However, Me doesn't seem to reffer to the combobox so the above doesn't work
Unfortunatelly writing what you suggest is the same as the original problem
code. It still forces me to exicitly specify the object (ComboBox1) in the
code. I hence have to repeat this line, and each time with a small name
change, in all my combo boxes increasing the maintenance burden of my code
which is what I want to reduce.


Sub ComboBox1_Click()
doSomething(Me.ComboBox1.Value)
End Sub


in your example what object does Me reffer to

 

[AllSensibleNamesTaken]

Nice one Tim,

So let me see if I've understood the technique in your link correctly

Step 1 - A ButtonGroup class is declared that effectively inherits from
ComandButton including its events

Step 2 - You can then go about overriding the event methods. I am assuming
that I will have access to the Me pointer within these, and that it will
point to the instance of the class that called the event, but haven't tried
it yet.

Step 3 - Then somewhere in the code you have to insert CommnadButton objects
into instances of the new class for them to have the behaviour of the new
class.


Is my understanding correct? If so it's a pitty that step 3 has to be
exlicitly coded by the user, but fair enough, I like your style and will be
trying it out.

Thanks so much for your help

 

[Tim Williams]

I think John's explanation is pretty clear: not sure I could improve on it.
As for access to "Me" - you'll have to test that out: not sure what it would
add though.

Tim