Excel VBA - getting name of range. - Page 2

Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Excel VBA - getting name of range.

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

    Default Re: Excel VBA - getting name of range.

     
    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:

    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 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
                #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 If
        
    End Sub
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


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

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

    Default Re: Excel VBA - getting name of range.

    Quote Originally Posted by nurulnabihahabdullah View Post
    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......
    I would recommend that you start a new thread and post this question which has nothing to do with the subject in hand.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


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

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

    Default Re: Excel VBA - getting name of range.

    Quote Originally Posted by MacroEcon1337 View Post
    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
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


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

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

    Default Re: Excel VBA - getting name of range.

    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:

    Code:
    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:

    Code:
    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
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


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

  5. #15
    New Member
    Join Date
    Mar 2017
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA - getting name of range.

      
    Yikes,

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

    MC

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
  •  

 

 
DMCA.com