VBA: Get the name of the clicked control

Posted on 2008-11-09
Last Modified: 2013-12-26

With a userform, I'd like to get the name of the clicked control automatically.
So for:

  Private Sub LeftHeaderBox2_Click()
    Set HeaderBox = Me.LeftHeaderBox2: Call FillProposalHeaderBox(HeaderBox)
   ' What can I replace 'Me.LeftHeaderBox2' with?
   ' Would think 'Me.ActiveControl' -- but 'Msgbox Me.ActiveControl.Name' names the frame that the
  '  label is in.

 End Sub

The current situation is only inconvenient because I have tons of clickable labels in my userforms.

Thanks for your help.

Question by:bishop3000
    LVL 11

    Accepted Solution

    Labels can't get the focus so it follows that they can never be the active control. The simplest way to do what you want is to use a module-wide string variable which you change in each label's click event, like so:

    Dim sHeader as String

    Private Sub lblHeader1_Click()
        sHeader = lblHeader.Caption
    End Sub

    LVL 11

    Expert Comment

    It's a pity VBA doesn't have control arrays a la VB proper as that would make it a piece of cake.
    LVL 11

    Expert Comment

    The only other method I can think of would rely on the labels being uniformly distributed and sized on their parent controls, in which case you could use the X and Y coordinates of the parent control's MouseDown event to calculate which label has been clicked on and set the string accordingly from a predefined array. But that might end up being as much work as setting the string in the labels' click events. And such a method would add a big overhead to the task if you ever needed to reposition the labels.

    Author Comment

    Hi Antagony1960,
    Thanks for your feedback.
    Definitely a moment of impotence for me...when the name of the control is in the procedure title itself but VBA won't tell me the control's name.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now