|
Event Handlers: Getting a handle to the calling object
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 |
Event Handlers: Getting a handle to the calling object
|
Event Handlers: Getting a handle to the calling object
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? "Bob Phillips" wrote: 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? -- __________________________________ HTH Bob "Nigel" wrote in message ... Create a Class Module -- Regards, Nigel "AllSensibleNamesTaken" wrote in message ... 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 |
Event Handlers: Getting a handle to the calling object
... 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 -- Jim Cone Portland, Oregon USA |
Event Handlers: Getting a handle to the calling object
Tried this?
http://www.j-walk.com/ss/excel/tips/tip44.htm Tim "AllSensibleNamesTaken" wrote in message ... 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? "Bob Phillips" wrote: 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? -- __________________________________ HTH Bob "Nigel" wrote in message ... Create a Class Module -- Regards, Nigel "AllSensibleNamesTaken" wrote in message ... 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 |
Event Handlers: Getting a handle to the calling object
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 "Jim Cone" wrote: ... 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 -- Jim Cone Portland, Oregon USA |
Event Handlers: Getting a handle to the calling object
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" wrote: Tried this? http://www.j-walk.com/ss/excel/tips/tip44.htm Tim "AllSensibleNamesTaken" wrote in message ... 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? "Bob Phillips" wrote: 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? -- __________________________________ HTH Bob "Nigel" wrote in message ... Create a Class Module -- Regards, Nigel "AllSensibleNamesTaken" wrote in message ... 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 |
Event Handlers: Getting a handle to the calling object
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 "AllSensibleNamesTaken" wrote in message ... 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" wrote: Tried this? http://www.j-walk.com/ss/excel/tips/tip44.htm Tim "AllSensibleNamesTaken" wrote in message ... 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? "Bob Phillips" wrote: 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? -- __________________________________ HTH Bob "Nigel" wrote in message ... Create a Class Module -- Regards, Nigel "AllSensibleNamesTaken" wrote in message ... 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 |
| All times are GMT +1. The time now is 05:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com