?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.

 

[tusharm]

One way to handle this is to use a class module and associate the actual control with a 'withevents' object in the class module.

Another might be to create a procedure with a parameter that is of type 'control' and call it from the different event procedures. Untested example:

sub xyz_change()
    do_stuff (me.xyz)
    end sub
sub do_stuff(x as msforms.control)
    msgbox x.name
    end sub

 

[Norie]

What type of controls are these are were are they located?

 

[mikerickson]

You might look at Application.Caller

 

[Default300]

@Tushar, Mike and Norie. Hi. Thanks for your replies.

It looks like there is no simple Keyword anyway.

@Norie: Like I say, I do not have a real example at this time. But for argument's sake lets take the two I suggested in my example: (1x ComboBox and 1x Label) both taken from the Standard VBA Control ToolBox (?ActiveX Controls) and placed on a Standard UserForm (Modeless).

@Tushar: (BTW. Thanks for invaluable advice on your website that solved another problem I posted about previously, involving automatically updating filepath links on workbook save / open. I creditted you I think but can't find where today.)

Tushar, the example you give is very similar to what I might want to achieve, in stripped down form, but the step I was hoping that VBA would take care of was to automatically return the "xyz" highlighted in Red (ie the parameter passed to your second sub), given that it knows the "xyz" highlighted in Blue (in the title/name of the first sub). If it can't do that automatically, then I would have to type / hard code "xyz" or "abc" etc for each control, just as I do now.

[FONT=Courier New]sub [COLOR=Blue][B]xyz[/B][/COLOR]_change()
    do_stuff (me.[COLOR=Red][B]xyz[/B][/COLOR])
    end sub[/FONT]

IDEA: Is there a way to return the Name of a Sub from within a Sub? If so then I could use Find and Mid, Left & Right to return everything left of the underscore.

Unfortunately, I have no experience with the use of Class Modules as yet, and there are none in my project(s). A little scared still!!

@Mike: Ditto for Application.Caller!!

One or both might well be the way to go though if I'm feeling brave.

 

[Norie]

This might be a strange question, but why do you need the name?

You definitely won't be able to use Application.Caller with a userform.

 

[billbrunt]

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.

 

[Legacy 98055]

If your controls are residing on a userform, you might find the ActiveControl property useful.

 

[Default300]

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

In a Standard Module, Declarations section...

[FONT=Courier New]Public [/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New](1 To 12, 1 To 7) As Variant[/FONT]

In UserForm Module (UserForm_Initialize Event)...

[FONT=Courier New]Private Sub UserForm_Initialize()
    
'<<<...Code Preceeds This...
    
    Dim bytLoop1 As Byte
    Dim bytLoop2 As Byte
    
'[+]    [EDIT]  Edit Comment
'(i)    Assign Values To ?Module / ?Project Level Variables:

'(i)    Array Declared As Follows:
'       [Public varUFCPCCboBoxes (1 To 12, 1 To 7) As Variant]

'       Array Columns Hold The Following Data:      Initialised As Follows:

'        (1) ComboBox Index Number                  Store Index Number Top To Bottom (Corresponds To Number In Name Of ComboBox Control)
'        (2) ComboBox Name                          Store Name String
'        (3) ComboBox BackColor                     Store Current Value
'        (4) !                                      Do Nothing (Spare)
'        (5) Worksheet Range Corresponding          Set Initial Value (Nothing)
'        (6) ComboBox Old Value                     Set Initial Value (0)
'        (7) ComboBox Pending Value                 Set Initial Value (0)
'        (8) ComboBox New Value                     Set Initial Value (0)
'        (9) ComboBox Pending Value Differential    Set Initial Value (0)
'       (10) Label1Front Name                       Store Name String
'       (11) Label2Front Name                       Store Name String
'       (12) LabelRear Name                         Store Name String


    For bytLoop1 = LBound(varUFCPCCboBoxes, 1) _
        To UBound(varUFCPCCboBoxes, 1)
        
        For bytLoop2 = LBound(varUFCPCCboBoxes, 2) _
            To UBound(varUFCPCCboBoxes, 2)
        
            Select Case bytLoop1
            
                Case 1
                
                    Let varUFCPCCboBoxes(bytLoop1, bytLoop2) = bytLoop2
                
                Case 2
                
                    Let varUFCPCCboBoxes(bytLoop1, bytLoop2) = "cboContactsCategory" & bytLoop2 & "Counter"
                
                Case 3
                
                    Let varUFCPCCboBoxes(bytLoop1, bytLoop2) = _
                        Me.Controls(varUFCPCCboBoxes(1, bytLoop2)).BackColor
                
                Case 4
                
                    'Spare: Do Nothing
                
                Case 5
                    
                    Set varUFCPCCboBoxes(bytLoop1, bytLoop2) = Nothing
                    
                Case 6, 7, 8, 9
                
                    Let varUFCPCCboBoxes(bytLoop1, bytLoop2) = 0
                
                Case 10
                
                    Let varUFCPCCboBoxes(bytLoop1, bytLoop2) = "lblContactsCategory" & bytLoop2 & "Front1"
                
                Case 11
                
                    Let varUFCPCCboBoxes(bytLoop1, bytLoop2) = "lblContactsCategory" & bytLoop2 & "Front2"
                
                Case 12
                
                    Let varUFCPCCboBoxes(bytLoop1, bytLoop2) = "lblContactsCategory" & bytLoop2 & "Rear"
                
            End Select
                
        Next bytLoop2
        
    Next bytLoop1


'(i)    Reset Loop Variables
    
    Let bytLoop1 = 0
    Let bytLoop2 = 0


'...Code Follows This...>>>

End Sub[/FONT]

In UserForm Module (ComboBox_Change and ComboBox_Click Events)...

[FONT=Courier New]Private Sub cboContactsCategory1Counter_Change()

'(i)    Contact Type: "Indirect"

    If blnDisableEventsFormInitializePhase = True Then
    
        Exit Sub

    End If

'(i)    ComboBox New Value: Assign To Array
'[NB]   (Pending Column = 3, This ComboBox Row = 1)

'[NB]   Val Function: Converts String To Number:
'       DataType Of ComboBox Value Is String
'       DataType Required For Array Variable Is Number (To Allow "=" Comparison Operations) (Auto Assigned As Double)

    Let [/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New](7, 1) = Val(cboContactsCategory1Counter.Value)
        
    Call gnlControlStatusFormatRefreshContactControls

End Sub


Private Sub cboContactsCategory1Counter_Click()

'(i)    Contact Type: "Indirect"

    If blnDisableEventsFormInitializePhase = True Then
    
        Exit Sub

    End If
    
'(i)    Control ForeColor: Change To Black
'       (For Legibility Of DropDown If ForeColor Same As BackColor) (Method Used To Hide Zero Values)

    Let cboContactsCategory1Counter.ForeColor = 1               '(black)
    Let cboContactsCategory1Counter.Font.Bold = True
    
End Sub[/FONT]

Also In UserForm Module...

[FONT=Courier New]Public Sub gnlControlStatusFormatRefreshContactControls()

    Dim bytLoop1 As Byte
    
    Let blnChangesPendingContactsUserForm = False
    
    For bytLoop1 = LBound(varUFCPCCboBoxes, 2) _
        To UBound(varUFCPCCboBoxes, 2)
        
        If varUFCPCCboBoxes(7, bytLoop1) <> _
            varUFCPCCboBoxes(6, bytLoop1) Then

            Let varUFCPCCboBoxes(9, bytLoop1) = _
                varUFCPCCboBoxes(7, bytLoop1) - _
                varUFCPCCboBoxes(6, bytLoop1)
            
            Let blnChangesPendingContactsUserForm = True
            
            Let Me.Controls(varUFCPCCboBoxes(10, bytLoop1)).ForeColor = &H80000008                'blackish   (Front1)
            Let Me.Controls(varUFCPCCboBoxes(11, bytLoop1)).ForeColor = &H80000008                'blackish   (Front2)
            Let Me.Controls(varUFCPCCboBoxes(12, bytLoop1)).BackStyle = fmBackStyleOpaque         'cream      (Rear)
            
        Else
            
            Let varUFCPCCboBoxes(9, bytLoop1) = 0
            
            Let Me.Controls(varUFCPCCboBoxes(10, bytLoop1)).ForeColor = &HFFFFFF                  'white      (Front1)
            Let Me.Controls(varUFCPCCboBoxes(11, bytLoop1)).ForeColor = &HFFFFFF                  'white      (Front2)
            Let Me.Controls(varUFCPCCboBoxes(12, bytLoop1)).BackStyle = fmBackStyleTransparent    'black      (Rear)
            
        End If
        
        
        With Me.Controls(varUFCPCCboBoxes(2, bytLoop1))                                           'combobox
        
            If varUFCPCCboBoxes(7, bytLoop1) = 0 Then
            
                Let .ForeColor = .BackColor                     'invisible
            
            Else
            
                Let .ForeColor = 1                              'black
            
            End If
            
        End With
        
        
    Next bytLoop1


    If blnChangesPendingContactsUserForm = True Then
        
        Me.cmdContactsLoad.BackColor = &H0&                     'black
        Me.cmdContactsLoad.ForeColor = &H80C0FF                 'tan
        Me.cmdContactsLoad.Enabled = False
        
        Me.cmdContactsEdit.BackColor = &H0&                     'black
        Me.cmdContactsEdit.ForeColor = &H80C0FF                 'tan
        Me.cmdContactsEdit.Enabled = False
        
        
        Me.cmdContactsSave.BackColor = &H8000&                  '?dark green
        Me.cmdContactsSave.ForeColor = &HFF00&                  '?bright green
        Me.cmdContactsSave.Enabled = True
        
        Me.cmdContactsCancel.BackColor = &HFF&                  '?red
        Me.cmdContactsCancel.ForeColor = &H80FFFF               '?yellow
        Me.cmdContactsCancel.Enabled = True

    Else
    
'[+]    [EDIT]  Change Colours For Load And Save Buttons (Active State)

        Me.cmdContactsLoad.BackColor = &H8000&                  '?dark green
        Me.cmdContactsLoad.ForeColor = &HFF00&                  '?bright green
        Me.cmdContactsLoad.Enabled = True
        
        Me.cmdContactsEdit.BackColor = &HFF&                    '?red
        Me.cmdContactsEdit.ForeColor = &H80FFFF                 '?yellow
        Me.cmdContactsEdit.Enabled = True

    
        Me.cmdContactsSave.BackColor = &H0&                     'black
        Me.cmdContactsSave.ForeColor = &H80C0FF                 'tan
        Me.cmdContactsSave.Enabled = False
        
        Me.cmdContactsCancel.BackColor = &H0&                   'black
        Me.cmdContactsCancel.ForeColor = &H80C0FF               'tan
        Me.cmdContactsCancel.Enabled = False

    End If

End Sub[/FONT]

In Worksheet Module (Worksheet_SelectionChange Event)...

[FONT=Courier New]'<<<...Code Preceeds This...[/FONT]

[FONT=Courier New]    For bytLoop1 = LBound([/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New], 2) _
        To UBound([/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New], 2)
        
'(i)    Contacts Group In Focus: Load Each Cell Range (Into Individual  Variables)

        Set [/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New](5, bytLoop1) = _
            rngCurrentContactsGroupInFocusWorksheet.Cells(1, bytLoop1)
            
'(i)    Contacts Group In Focus: Load Each Cell Value (Into Individual  Variables) (Initial Value Holder)

        Let [/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New](6, bytLoop1) = _
            [/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New](5, bytLoop1).Value

'(i)    Contacts Group In Focus: Load Each Cell Value (Into Individual  Variables) (Pending Value Holder)

        Let [/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New](7, bytLoop1) = _
            [/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New](5, bytLoop1).Value


'[+]    [EDIT]  Add Code for New Values


'(i)    Contacts Group In Focus: Load Each Cell Value (Into UserForm  ComboBox Controls)
'       Reformat Controls To Hide Zero Values

        With UserForms(0).Controls([/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New](2, bytLoop1))
        
            .Value = [/FONT][FONT=Courier New]varUFCPCCboBoxes[/FONT][FONT=Courier New](6, bytLoop1)
            
            If .Value = 0 Then
            
                .ForeColor = .BackColor
            
            Else
            
                .ForeColor = 1
                .Font.Bold = True
            
            End If
        
        End With
        
    Next bytLoop1

'...Code Continues...[/FONT]>>>

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...

 

[SamTYler]

Excel thinks the first dimension is Rows and the second Columns.

Set a Range = Array and see.

Sheets(1).Range("A1")=My2DArray

Another way to look at it is as a record. Each FirstDimension(i), with its associated second dimension, is one complete record and each SecondDimension(i) is a single field.