?How To: Return Name of Control from Within that Control's Event Procedure

[Default300]

Is there a Keyword (such as "Me" or "Parent" etc.) that I could use to Return the Name Property of a Control from within that Control's Event Code.

For example:

[FONT=Courier New]Private Sub cboComboBox1_Change()

    Dim strControlNameCurrent As String
    Dim strControlNameOther As String

    [COLOR=SeaGreen]'With [/COLOR][COLOR=Red]?MeMyself_ieControlObject_NotUserForm[/COLOR]

        strControlName[/FONT][FONT=Courier New]Current[/FONT][FONT=Courier New] = .Name

        [COLOR=SeaGreen]'aim is for above line to return "cboComboBox1"[/COLOR]

        strControlNameOther = "lbl" & Mid(strControlName, 4, Len(strControlName - 3))
        
        strControlNameOther.BackColor = 3

    [COLOR=SeaGreen]'End With[/COLOR]

End Sub[/FONT][FONT=Courier New]
[/FONT]

I am hoping that if this were possible, I could make code blocks more generic and reuse them for several controls, whether in separate Subs or Looping through Object Collections, or Grouped Controls.

I would only want to do this within the event code of the control whose name I want to use.

If there's a quick answer to the question, great!

If not then, besides the simulated example given above, I cannot give more details on what I (would be) trying to achieve. At the moment, I don't have a real world example for people to advise me on workarounds. If there's no obvious answer, then I'll give details / reask the question next time a concrete example of the issue arises.

Thanks.

 

[Default300]

Thanks Sam.

I checked a couple of Worksheet Functions too (Index, Offset, and Address). All conform to the (Row, Column) convention. I could have sworn there was an exception or two, but can't find them!!

I opted for the opposite in my code because I was carrying out the same operation on one field for several records/controls. Thus the more important bit of information was the column. And so I placed it first, to see the funtionality of the code at a glance.

Still I prefer to follow conventions if they exist (in VBA that is, not life in general!), so I may change it to make it easier for someone else to interpret. On the other hand, in my case I'm not even talking about a range. The two dimensional concept is quite abstract, or metaphorical, and could just as easily be transposed.

Thanks for the advice.

 

[Akihito Yamashiro]

Assuming you have 'lbl1' and 'cmb1' and 'lst1' , the generic way is the following:
Step 1: Create a class named 'CtlChangeHandler' or else.
It's code is:

Private WithEvents cmb As msforms.ComboBox
Private WithEvents lst As msforms.ListBox
Public Property Set Control(ByVal lhs As Object) 'lhs : Left-Hand-Side
    Set cmb = Nothing
    Set lst = Nothing
    If TypeName(lhs) = "ComboBox" Then
        Set cmb = lhs
    ElseIf TypeName(lhs) = "ListBox" Then
        Set lst = lhs
    End If
End Property
Private Sub generic_Change(ByVal obj As Object)
    Dim strControlNameCurrent As String
    Dim strControlNameOther As String
 
    strControlNameCurrent = obj.Name
 
    strControlNameOther = "lbl" & Mid(strControlNameCurrent, 4, Len(strControlNameCurrent) - 3)
 
    obj.Parent.Controls(strControlNameOther).BackColor = 3
End Sub
Private Sub Class_Terminate()
 Set cmb = Nothing
 Set lst = Nothing
End Sub
Private Sub cmb_Change()
 generic_Change cmb
End Sub
 
Private Sub lst_Change()
 generic_Change lst
End Sub

Step 2: Add the followings to the User form:

Private CtlChangeHandlerInstance() As CtlChangeHandler
Sub SetCtlChangeHandler()
    ReDim CtlChangeHandlerInstance(0 To Me.Controls.Count - 1)
    Dim i As Long
    For i = 0 To Me.Controls.Count - 1
    Debug.Print Me.Controls(i).Name, TypeName(Me.Controls(i))
        Set CtlChangeHandlerInstance(i) = New CtlChangeHandler
        Set CtlChangeHandlerInstance(i).Control = Me.Controls(i)
    Next
End Sub
Sub ReleaseCtlChangeHandler()
    Dim i As Long
    For i = 1 To Me.Controls.Count - 1
        Set CtlChangeHandlerInstance(i) = Nothing
    Next
End Sub
 
Private Sub UserForm_Terminate()
    ReleaseCtlChangeHandler
End Sub
Private Sub UserForm_Initialize()
    Me.lst1.AddItem "abc" 'an example
    Me.lst1.AddItem "def" 'an example
    Me.cmb1.AddItem "abc" 'an example
    Me.cmb1.AddItem "def" 'an example
    SetCtlChangeHandler
End Sub

The point is you should write a class code which can handle events and make an array of that class.

 

[Akihito Yamashiro]

Sorry , ReleaseCtlChangeHandler has a bug.
The variable 'i' should be 0 when the for loop begins.

The right code is:

Sub ReleaseCtlChangeHandler()
    Dim i As Long
    For i = 0 To Me.Controls.Count - 1
        Set CtlChangeHandlerInstance(i) = Nothing
    Next
End Sub

 

[Default300]

Hi Akihito,

Just a quick reply to thank you for your input and what looks like pretty comprehensive advice. I really appreciate it.

I'm working on another section right now, but will definitely dissect what you've suggested, try to understand it, and possibly implement it.

As you may know, I'm an amateur enthusiast as opposed to a trained programmer, but ever eager to learn and to work towards best practice.

So far I haven't attempted Class Modules, used WithEvents, or created Properties; but I know this is the way to go for certain scenarios.

Cheers.

PS: Akihito is your forename, not surname, right?

 

[tusharm]

Except for diagnostic purposes, I've never cared how my routines are called. Modularize code and have the subroutines/functions operate only on the arguments passed to them. Now, how they are called or who calls them becomes irrelevant.

I think, but am not sure, this question might be related.

I've got many routines calling subroutines which call subroutines, etc. It would help to know all the calls, i.e. if Sub1 calls Sub A which calls Sub i which calls function xyz, ideally I could see

1->A->i

Is this possible?

I tried Application.Caller and it looks like it show 1 but not A and i.

 

[tusharm]

If this works for you, go for it!

If it had been me, I might have invested the time it must have taken you to write and debug the code to learn about classes and event variables. But, that's me. {grin}

Re the use of Let, again if it works for you, go for it. Just remember that in .Net neither Set nor Let are allowed.

As far as 2D matrixes go, most people, including myself, think of rows as the 1st dimension.

Hi.

In case anyone's interested, I've decided to go with a Public Array Variable, with 2 dimensions, of Variant DataType.

If you imagine the second dimension as Rows, then each Row holds information related to 1 of the 7 near identical ComboBox Controls.

If you imagine the first dimension as Columns, then each Column holds information on one of 12 Parameters.

The Parameters include information like the name of the ComboBox control (string), the names of the 3 corresponding Labels (strings), the original and pending values of the ComboBox, the default BackColor and ForeColor of the Controls etc. (More details in Initialize Event)

This way I can use a loop to iterate through each of the 7 ComboBox controls, reading changes in Values and adjusting format of Labels accordingly, or setting variables concerned with saving or discarding changes prior to moving to a new record.

Code as follows (Sorry I use very long variable names! Try Print View if code window is too restrictive to see full line. PS I compacted "varUserFormControlPropertiesContactsComboBoxes" down to "varUFCPCCboBoxes" for this post!):

{snip of code}

By the way, regarding 2-dimensional arrays, is it more typical to think of the first dimension as Rows/Records and the second as Columns/Fields or is the reverse more common? Is there a convention, or is it a matter of personal preference?

Thanks for those who offered advice. Shame there's no keyword, but this method will suffice as a workaround, as long as I remember which "Column" holds which parameter! And I don't think that the memory used will be significant (correct me if I'm wrong).

PS. I've recently opted to use the optional word "Let" for clarity. It may be a fad. Cheers...

 

[Default300]

Hi Tushar.

Thanks for the heads-up about .Net. I'd prefer my work to be more easily poratable to .Net, or at least not create unnecessary complications. Can I clarify something though. Unlike "Let", "Set" is NOT optional in VBA6 / VBA6.5, is it?

Re "Let": I think I just like the way it lights up as a keyword!! (and emphasises at a glance that it's a method, not a property check). It also might be linked to when I used to programme my ZX81 and Spectrum in Basic in 1984 or so!!

Re Classes etc. I definitely will invest that time eventually. And I'm sure the time invested would pay dividends. But unfortunately, this project is way overdue. I'm on version 1018. It has taken me about two years (20+ hours per week recently)!! And that's in addition to my full time job which has nothing to do with computers.

Thanks for your help and advice now and in the past.

 

[Akihito Yamashiro]

PS: Akihito is your forename, not surname, right?

Thanks for interested in my name.
Akihito is my forename.

 

[xenou]

Unlike "Let", "Set" is NOT optional in VBA6 / VBA6.5, is it?

Right. But not used in .Net either.

 

[Default300]

@xenou

Thanks.