Excel VBA - getting name of range.

[MacroEcon1337]

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?

Private Sub Label19_Click()


Dim ButtonName As Variant
ButtonName = Me.ActiveControl.name


MsgBox ButtonName


Exit Sub

 

[Jaafar Tribak]

For the sake of completness and in case anyone uses the above code in the future, here is a slight correction that prevents the code erroring out should the userform have no labels on it.

UserForm Module:

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]          [COLOR=#008000]'*[/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 iLabelsCount As Integer
    
    For Each Ctl In Me.Controls
        If TypeName(Ctl) = "Label" Then
            ReDim Preserve ArLabels(iLabelsCount)
            ReDim Preserve ArLabelCaptions(iLabelsCount)
            Set ArLabels(iLabelsCount) = Ctl
            ArLabelCaptions(iLabelsCount) = Ctl.Caption
            iLabelsCount = iLabelsCount + 1
        End If
    Next Ctl
    
    If iLabelsCount Then
        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 If
    
End Sub

 

[Jaafar Tribak]

Hi im in trouble,

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

<tbody>
</tbody>

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

I would recommend that you start a new thread and post this question which has nothing to do with the subject in hand.

 

[Jaafar Tribak]

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.

I'll post an example in a moment

 

[Jaafar Tribak]

Here is an example that uses a class module:

1- Add a new Class Module to your VBProject and keep its default name Class1 (From the VBE menu, click on on Insert >Class Module)

Now, place this code in the Clas module code pane:

Option Explicit

Public WithEvents label As MSForms.label

Private Sub label_Click()
    MsgBox "You clicked Label : '" & label.Name & "'"       '*
End Sub

2- Place the followng code in the Userform module:

Option Explicit

Private oLabelsCollection As New Collection

Private Sub UserForm_Initialize()
    Dim oCtl As Control
    Dim oClassInstance As Class1
    
    For Each oCtl In Me.Controls
        If TypeName(oCtl) = "Label" Then
            Set oClassInstance = New Class1
            Set oClassInstance.label = oCtl
            oLabelsCollection.Add oClassInstance
        End If
    Next
End Sub

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.

I would recommend you check out this nice tutorial by JKP :
http://www.jkp-ads.com/Articles/ControlHandler02.asp

 

[MacroEcon1337]

Yikes,

I missed your reply. Finally circled back to this dilemma again - and voila - your code works. Thank you very much!

MC