Any way to avoid hardcoding control name inside the control event procedure?

Just curious. Is there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?

It might be clearer to explain by a dummy code example:

Please Login or Register  to view this content.

(I'm seeking what I would need to replace Line1 with)

What are you wanting to do with the control?

Originally Posted by Richard Buttrey

What are you wanting to do with the control?

I want to reuse the same event procedure code for multiple controls on the same form. If I can have the control self reference itself, I can just copy/paste the same code into each controls procedure. Otherwise I will need to edit each copy to match that controls name.

Hi,

Yes I understand that, but then assuming you can self reference the control what are you wanting to happen? Are you trying to change the controls properties for instance?

Originally Posted by mc84excel

I want to reuse the same event procedure code for multiple controls on the same form.

You should be looking at using a class for that.

Originally Posted by Richard Buttrey

Hi,

Yes I understand that, but then assuming you can self reference the control what are you wanting to happen? Are you trying to change the controls properties for instance?

No. I want all these controls to call the same procedure. The procedure will behave slightly different depending on which control called it.

I was thinking of adding a string argument to the called procedure and in the controls procedure have something like below (dummy code):

Please Login or Register  to view this content.

Of course I would still have to hard-code each controls name in the called procedure (in the Select Case section).

Unless... What if I set a numbered tag to each control and then pass that as the argument? Or if I used RIGHT on the .Name to obtain a number and pass that? That would avoid hard-coding the control names in the called procedure.
In any case, I'd still need the code to self reference the control (unless I want to edit each procedure after I copy the code - which I don't )


I did look into using ActiveControl instead but that can't be used for Label controls (I need Labels as pseudo CommandButtons to create a toggled effect)

Declare a collection with global scope. It will contain all of the controls handled by the same procedure.

Create a class module to process the event of interest. Here's one for textboxes that must contain numeric strings for positive whole numbers:

Please Login or Register  to view this content.

When the form initializes, create a class instance for each relevant textbox and add it to the collection.

Please Login or Register  to view this content.

I need Labels as pseudo CommandButtons to create a toggled effect

Why not use the toggle button control?

As suggested the class approach is the way to go. Within the class you can obtain the controls name or tag or someother property in order to determine what code to execute.

Originally Posted by Andy Pope

Why not use the toggle button control?

Slightly off topic for this thread - That wouldn't work for the form I'm designing - the pseudo label buttons have a picture (thanks for your help on that in another thread ) and act as "switches" between different sections of the form (by choosing different MultiPage and the style set to no tabs). So when a different section of the main form is chosen, that label goes sunken and disabled (all other pseudo buttons go raised and enabled) and then the multipage switches to the appropriate page. It's difficult to explain in words alone but I can assure you the end effect looks quite nice.

Originally Posted by Andy Pope

As suggested the class approach is the way to go. Within the class you can obtain the controls name or tag or someother property in order to determine what code to execute

For a practical solution to this thread I may have to go down the class route (as recommended by yourself, shg & Norie). But I have theoretical curiosity as to whether the VBA language would allow you to create a 'self reference' to a control within that controls event procedure. The equivalent of Me!ControlName.

Your code looks interesting shg. I will probably modify this to get a practical solution right now.

For my theoretical knowledge, I'm still curious as to whether or not the VBA language allows a control to 'self-reference' itself without hard-coding the control name within the event procedure.

No, it doesn't, you'd need to raise an event that passes in a reference to the object that has changed. As an example the worksheet change event passes in a reference to the range that has changed. The reason for this is simply that the event procedure isn't "the event" it is simply a sub that is run in response to the event being raised (so Me will always be the object from which the code is run - it has no concept of which control raised the event), the arguments are passed in byref so that when the sub finishes the code returns back to the code in the event raising object and this code can then work with the changed (or not) event arguments.

To resolve your particular issue, it would be possible to take shg's code and pass a reference to the userform into it as well as a reference to the textbox. Create a public sub that takes as an argument a textbox object in the userform. Then simply call this from the created event handling class through the reference to the userform. You'd need to then be mindful of creating circular references and make sure that all your objects are disposed of properly.

I have given you at least 1 example of this working in practice when you wanted to create a fancy dropdown box with controls in

Originally Posted by Kyle123

No, it doesn't, you'd need to raise an event that passes in a reference to the object that has changed. As an example the worksheet change event passes in a reference to the range that has changed. The reason for this is simply that the event procedure isn't "the event" it is simply a sub that is run in response to the event being raised (so Me will always be the object from which the code is run - it has no concept of which control raised the event), the arguments are passed in byref so that when the sub finishes the code returns back to the code in the event raising object and this code can then work with the changed (or not) event arguments.

Pity. It would have been handy.

Originally Posted by Kyle123

To resolve your particular issue, it would be possible to take shg's code and pass a reference to the userform into it as well as a reference to the textbox. Create a public sub that takes as an argument a textbox object in the userform. Then simply call this from the created event handling class through the reference to the userform. You'd need to then be mindful of creating circular references and make sure that all your objects are disposed of properly. I have given you at least 1 example of this working in practice when you wanted to create a fancy dropdown box with controls in

I hadn't forgotten that you did that (That code proved to be very useful in that project)
In fact, I had been thinking there would be less for me to modify in your code as opposed to the code provided by shg (no offence intended to shg)

And it's solved. I've worked out a way whereby every command button can be set up to call another sub on the same form without needing to hardcode the button name in the call

e.g.

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Find the solution at http://www.excelforum.com/excel-prog...t-clicked.html