Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Excel VBA - getting name of range.

  1. #1
    Board Regular
    Join Date
    Mar 2017
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel VBA - getting name of range.

    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

  2. #2
    Board Regular
    Join Date
    Mar 2017
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - getting name of range.

    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.

    Quote Originally Posted by MacroEcon1337 View Post
    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

  3. #3
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    6,700
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel VBA - getting name of range.

    A label cannot be an active control so how is it possible that your first code works !!
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  4. #4
    Board Regular
    Join Date
    Mar 2017
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - getting name of range.

    Quote Originally Posted by Jaafar Tribak View Post
    A label cannot be an active control so how is it possible that your first code works !!
    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

  5. #5
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    6,700
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel VBA - getting name of range.

    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 by Jaafar Tribak; Nov 7th, 2017 at 11:31 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    6,700
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel VBA - getting name of range.

    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
    
    #If  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)
        #If  Win64 Then
             Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal arg1 As LongPtr, ppacc As Any, pvarChild As Variant) As Long
        #Else 
             Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
        #End  If
    #Else 
        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
    #End  If
    
    Private Const CHILDID_SELF = &H0&
    Private bXitLoop As Boolean
    
    '*************************************************************
    ' This is the generic label On_Click pseudo-event.          '*
    Private Sub GenericLabelClickEvent(ByVal Label As Control)  '*
        MsgBox "You clicked Label : '" & Label.Name & "'"       '*
    End Sub                                                     '*
    '*************************************************************
    
    Private Sub UserForm_Activate()
    
        'run any pre-existing form activation code here before starting the loop.
    
        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
            #If  Win64 Then
                Dim Ptr As LongPtr
                CopyMemory Ptr, tCurPos, LenB(tCurPos)
                Call AccessibleObjectFromPoint(Ptr, oIA, vKid)
            #Else 
                Call AccessibleObjectFromPoint(tCurPos.X, tCurPos.Y, oIA, vKid)
            #End  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 by Jaafar Tribak; Nov 7th, 2017 at 12:42 PM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  7. #7
    Board Regular
    Join Date
    Mar 2017
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - getting name of range.

    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,




    Quote Originally Posted by Jaafar Tribak View Post
    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
    
    #If  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)
        #If  Win64 Then
             Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal arg1 As LongPtr, ppacc As Any, pvarChild As Variant) As Long
        #Else 
             Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
        #End  If
    #Else 
        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
    #End  If
    
    Private Const CHILDID_SELF = &H0&
    Private bXitLoop As Boolean
    
    '*************************************************************
    ' This is the generic label On_Click pseudo-event.          '*
    Private Sub GenericLabelClickEvent(ByVal Label As Control)  '*
        MsgBox "You clicked Label : '" & Label.Name & "'"       '*
    End Sub                                                     '*
    '*************************************************************
    
    Private Sub UserForm_Activate()
    
        'run any pre-existing form activation code here before starting the loop.
    
        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
            #If  Win64 Then
                Dim Ptr As LongPtr
                CopyMemory Ptr, tCurPos, LenB(tCurPos)
                Call AccessibleObjectFromPoint(Ptr, oIA, vKid)
            #Else 
                Call AccessibleObjectFromPoint(tCurPos.X, tCurPos.Y, oIA, vKid)
            #End  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

  8. #8
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    6,700
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel VBA - getting name of range.

    1. These text labels are distributed across 15 different forms. Would I need to copy the below API code into each individual forms?
    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.
    I would recommend using a Class module -
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  9. #9
    Board Regular
    Join Date
    Mar 2017
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - getting name of range.

    Quote Originally Posted by Jaafar Tribak View Post
    Yes.


    I would recommend using a Class module -
    Do you suppose you could point me in the direction of some example code i could reference/modify... Class module is a totally new animal for me.

  10. #10
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - getting name of range.

    Hi im in trouble,
    Name Total
    Adsmi 1
    Adsjj 2
    Adshh 3
    Mahh 4
    Makk 57
    Maiis 9
    you 900
    Yopp 6
    Yodd 42

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •