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:

Private Sub CommandButton1_Click()
    With Me.Controls.Parent.Me.Child 'this would refer to CommandButton1 without hard-coding the control by name
        'do something with control
    End With
End Sub

(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 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):

Private Sub CommandButton1_Click()
    With Me.Controls.Parent.Me.Child 'this would refer to CommandButton1 without hard-coding the control by name
        Call MySharedProcedureExample(.name)
    End With
End Sub

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)

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.

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:

Option Explicit

' Constrains member textboxes to have non-negative whole number values only

Public WithEvents clsTxtNum As MSForms.TextBox

Public Property Set Control(txtNumNew As MSForms.TextBox)
    Set clsTxtNum = txtNumNew
End Property

Private Sub clsTxtNum_Change()
    Dim sVal        As String
    Dim bCodeChange As Boolean

    If bCodeChange Then Exit Sub
    If gbCodeChange Then Exit Sub

    sVal = clsTxtNum.Value

    If Len(sVal) = 0 Then GoTo Just0
    If Not IsNumeric(sVal) Then GoTo BadInp
    If Int(CDbl(sVal)) <> CDbl(sVal) Then GoTo BadInp

    wksQuo.Range(clsTxtNum.Name) = clsTxtNum.Value
    EnableControls
    SetLabels

OuttaHere:
    bCodeChange = False
    Exit Sub

BadInp:
    MsgBox Prompt:="Whole number numeric values only!", _
           Title:=gsTitle
Just0:
    bCodeChange = True
    clsTxtNum.Value = "0"
    GoTo OuttaHere
End Sub

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

            Select Case TypeName(ctl)
                Case "CheckBox"
                    Set clsChk = New clsEventSinkChk
                    Set clsChk.Control = ctl
                    gcolCtl.Add Item:=clsChk

                Case "OptionButton"
                    Set clsOpt = New clsEventSinkOpt
                    Set clsOpt.Control = ctl
                    gcolCtl.Add Item:=clsOpt

                Case "TextBox"
                    If LCase(ctl.Name) Like "txtnum*" Then
                        Set clsTxtNum = New clsEventSinkTxtNum
                        Set clsTxtNum.Control = ctl
                        gcolCtl.Add Item:=clsTxtNum
                    Else
                        Set clsTxt = New clsEventSinkTxt
                        Set clsTxt.Control = ctl
                        gcolCtl.Add Item:=clsTxt
                    End If

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.

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.

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.

Private Sub CommandButton1_Click()
    Call ThisFormsCommandButtonRedirect
End Sub

instead of

Private Sub CommandButton1_Click()
    Call ThisFormsCommandButtonRedirect(CommandButton1) 'note that argument would require either the control or the controls name
End Sub

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