Excel VBA - getting name of range.

M

MacroEcon1337

Board Regular
Joined
Mar 16, 2017
Messages
65
Seeking a simple VBA solution to to determine name of a text label object located on an excel Userform...

When this control is placed directly on a raw Userform - it works correctly by returning a message prompt saying "Label19" ..... But when the label is placed within a frame on the form..it returns the name of the frame ("Frame20") instead of its true name ("Label19")

Any ideas how to get this code to work for a text label located inside a frame?


Code: 
Private Sub Label19_Click()


Dim ButtonName As Variant
ButtonName = Me.ActiveControl.name


MsgBox ButtonName


Exit Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Can an admin edit the title to say something more descriptive?

"Excel VBA - how to retrieve name of text label object located on excel UserForm?"

im afraid the boring title will not entice the ninjas to come look.

MacroEcon1337 said:
Seeking a simple VBA solution to to determine name of a text label object located on an excel Userform...

When this control is placed directly on a raw Userform - it works correctly by returning a message prompt saying "Label19" ..... But when the label is placed within a frame on the form..it returns the name of the frame ("Frame20") instead of its true name ("Label19")

Any ideas how to get this code to work for a text label located inside a frame?


Code: 
Private Sub Label19_Click()


Dim ButtonName As Variant
ButtonName = Me.ActiveControl.name


MsgBox ButtonName


Exit Sub
Click to expand...
 
Upvote 0
A label cannot be an active control so how is it possible that your first code works !!
 
Upvote 0
Jaafar Tribak said:
A label cannot be an active control so how is it possible that your first code works !!
Click to expand...

Ah, the code I posted was actually taken from a command button. When the press the button, it tells you its name. Except it doesn't work when the button is on/in a frame - it returns the name of the frame instead.

Getting this script to work with a command button on a frame would be nice. Getting it to work with a text label, on a frame- if that is even possible - would be the best.

Any ideas greatly appreciated,

MC
 
Upvote 0
For a command button on a frame you could use this :
Code: 
Private Sub CommandButton1_Click()
    Dim ButtonName As Variant
    ButtonName = Me.ActiveControl.ActiveControl.Name
    MsgBox ButtonName
End Sub


For the Label why not return its name like :
Code: 
Private Sub Label1_Click()
    Dim Labelname As Variant
    Labelname = "Label1"
    MsgBox Labelname
End Sub


If you have several labels then you could use a Class module so you can have one generic click routine for all of them.
 
Last edited:
Upvote 0
If you have several labels inside frames controls or inside MultiPages or directly placed on the userform and you want to have a generic label Click event for all of the labels then, as I said earlier, using a Class module is the standard way to go.

An alternative to using a class module which keeps the whole code within the userform module and which I personally find more tidy is the following API-based workaround :

Place the following in the userform module :

Code: 
Option Explicit

Private Type POINTAPI
        X As Long
        Y As Long
End Type

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  Win64 Then
         Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal arg1 As LongPtr, ppacc As Any, pvarChild As Variant) As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
         Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    Private Declare Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

Private Const CHILDID_SELF = &H0&
Private bXitLoop As Boolean

[COLOR=#008000]'*************************************************************[/COLOR]
[COLOR=#008000]' This is the generic label On_Click pseudo-event.          '*[/COLOR]
Private Sub GenericLabelClickEvent(ByVal Label As Control)  [COLOR=#008000]'*[/COLOR]
    MsgBox "You clicked Label : '" & Label.Name & "'"       [COLOR=#008000]'*[/COLOR]
End Sub                                                     [COLOR=#008000]'*[/COLOR]
[COLOR=#008000]'*************************************************************[/COLOR]

Private Sub UserForm_Activate()

    [COLOR=#008000]'run any pre-existing form activation code here before starting the loop.[/COLOR]

    Call StartLoop
    
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    bXitLoop = True
End Sub

Private Sub StartLoop()
    Dim tCurPos As POINTAPI
    Dim oIA As IAccessible
    Dim vKid  As Variant
    Dim Ctl As Control
    Dim ArLabels() As Control
    Dim ArLabelCaptions() As String
    Dim i As Integer
    
    For Each Ctl In Me.Controls
        If TypeName(Ctl) = "Label" Then
            ReDim Preserve ArLabels(i)
            ReDim Preserve ArLabelCaptions(i)
            Set ArLabels(i) = Ctl
            ArLabelCaptions(i) = Ctl.Caption
            i = i + 1
        End If
    Next Ctl
    
    Do
        GetCursorPos tCurPos
        [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  Win64 Then
            Dim Ptr As LongPtr
            CopyMemory Ptr, tCurPos, LenB(tCurPos)
            Call AccessibleObjectFromPoint(Ptr, oIA, vKid)
        [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
            Call AccessibleObjectFromPoint(tCurPos.X, tCurPos.Y, oIA, vKid)
        [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If
        If Not IsError(Application.Match(oIA.accName(CHILDID_SELF), ArLabelCaptions, 0)) Then
            If GetKeyState(vbKeyLButton) = -127 Or GetKeyState(vbKeyLButton) = -128 Then
                Call GenericLabelClickEvent(ArLabels(Application.Match(oIA.accName(CHILDID_SELF), ArLabelCaptions, 0) - 1))
            End If
        End If
        DoEvents
    Loop Until bXitLoop
    
End Sub
 
Last edited:
Upvote 0
Comments

1. The "double active control" is brilliant - a good tip to know.

2. I have hundreds of text labels. I think class module (or the API technique you suggest below) is the way to go.

Questions:

1. These text labels are distributed across 15 different forms. Would I need to copy the below API code into each individual forms?

2. In terms of calculation speed - would either of these methods introduce substantial "calculation overhead" for other areas of the workbook? If the added calculation load is significant, it might be better for me to program the text captions by hand.

thank you your wisdom on this,




Jaafar Tribak said:
If you have several labels inside frames controls or inside MultiPages or directly placed on the userform and you want to have a generic label Click event for all of the labels then, as I said earlier, using a Class module is the standard way to go.

An alternative to using a class module which keeps the whole code within the userform module and which I personally find more tidy is the following API-based workaround :

Place the following in the userform module :

Code: 
Option Explicit

Private Type POINTAPI
        X As Long
        Y As Long
End Type

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  Win64 Then
         Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal arg1 As LongPtr, ppacc As Any, pvarChild As Variant) As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
         Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    Private Declare Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

Private Const CHILDID_SELF = &H0&
Private bXitLoop As Boolean

[COLOR=#008000]'*************************************************************[/COLOR]
[COLOR=#008000]' This is the generic label On_Click pseudo-event.          '*[/COLOR]
Private Sub GenericLabelClickEvent(ByVal Label As Control)  [COLOR=#008000]'*[/COLOR]
    MsgBox "You clicked Label : '" & Label.Name & "'"       [COLOR=#008000]'*[/COLOR]
End Sub                                                     [COLOR=#008000]'*[/COLOR]
[COLOR=#008000]'*************************************************************[/COLOR]

Private Sub UserForm_Activate()

    [COLOR=#008000]'run any pre-existing form activation code here before starting the loop.[/COLOR]

    Call StartLoop
    
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    bXitLoop = True
End Sub

Private Sub StartLoop()
    Dim tCurPos As POINTAPI
    Dim oIA As IAccessible
    Dim vKid  As Variant
    Dim Ctl As Control
    Dim ArLabels() As Control
    Dim ArLabelCaptions() As String
    Dim i As Integer
    
    For Each Ctl In Me.Controls
        If TypeName(Ctl) = "Label" Then
            ReDim Preserve ArLabels(i)
            ReDim Preserve ArLabelCaptions(i)
            Set ArLabels(i) = Ctl
            ArLabelCaptions(i) = Ctl.Caption
            i = i + 1
        End If
    Next Ctl
    
    Do
        GetCursorPos tCurPos
        [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  Win64 Then
            Dim Ptr As LongPtr
            CopyMemory Ptr, tCurPos, LenB(tCurPos)
            Call AccessibleObjectFromPoint(Ptr, oIA, vKid)
        [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
            Call AccessibleObjectFromPoint(tCurPos.X, tCurPos.Y, oIA, vKid)
        [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If
        If Not IsError(Application.Match(oIA.accName(CHILDID_SELF), ArLabelCaptions, 0)) Then
            If GetKeyState(vbKeyLButton) = -127 Or GetKeyState(vbKeyLButton) = -128 Then
                Call GenericLabelClickEvent(ArLabels(Application.Match(oIA.accName(CHILDID_SELF), ArLabelCaptions, 0) - 1))
            End If
        End If
        DoEvents
    Loop Until bXitLoop
    
End Sub
Click to expand...
 
Upvote 0
1. These text labels are distributed across 15 different forms. Would I need to copy the below API code into each individual forms?
Click to expand...
Yes.

2. In terms of calculation speed - would either of these methods introduce substantial "calculation overhead" for other areas of the workbook? If the added calculation load is significant, it might be better for me to program the text captions by hand.
Click to expand...
I would recommend using a Class module -
 
Upvote 0
Hi im in trouble,
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Name[/TD]
[TD="class: xl64, width: 64"]Total[/TD]
[/TR]
[TR]
[TD="class: xl65"]Adsmi[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Adsjj[/TD]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Adshh[/TD]
[TD="class: xl66, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]Mahh[/TD]
[TD="class: xl66, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]Makk[/TD]
[TD="class: xl66, align: right"]57[/TD]
[/TR]
[TR]
[TD="class: xl65"]Maiis[/TD]
[TD="class: xl66, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl65"]you[/TD]
[TD="class: xl66, align: right"]900[/TD]
[/TR]
[TR]
[TD="class: xl65"]Yopp[/TD]
[TD="class: xl66, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]Yodd[/TD]
[TD="class: xl68, align: right"]42
[/TD]
[/TR]
</tbody>[/TABLE]

Hi all, i need to sum up all the amount for the same name in front.
For example, first three row same name in front which is "ads****" which total will be 6.
and for name with "ma***" infront will be 70 and "Yo***" will be 948.
How can i do?
Help me......
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA Populate userform dynamically with custom class Frame
Replies
1
Views
346
saboh12617
S
E
  • Question Question
Split Workbook VBA w/Default Value for Sensitivity Labels
Replies
1
Views
2K
Jeffrey Mahoney
Jeffrey Mahoney
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
155
krunal123
K
alexofrhodes
  • Solved
Looping selected controls on a designer
Replies
5
Views
691
alexofrhodes
alexofrhodes
H
  • Question Question
VBA Project, Copy enabled labels and textboxes from a userform to another userform
Replies
0
Views
365
HosEsf
H

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top