Retrieve active control name from an excel user form after it's moused over

Question

I am trying to dynamically create an application that will use the name of a button (created at runtime) on the form, to extract a number from the end of it's name:

FundNo = Right(Me.ActiveControl.Name, 1)

It's not working and I suspect it's because my button isn't the active control as I am using the mouse over event to trigger it which I'm guessing doesn't give it the focus.

It's nothing to do with the data type it's just not returning the name of the control.

Does anyone have any ideas as to how I could do this as it will be really cool if I can get it to work?

Thanks and regards, Mark

Answer 1

You are right, the CommandButton is not the active Control when the mouse moves over it. It is possible to do what you want, but not in a single line of code. You need a WithEvents variable in a Class module to return the events from the CommandButtons ... the following is the process, assuming you already have your UserForm set up and it is correctly adding your CommandButtons ... the following uses "MyUserFormName" as the assumed name of your UserForm, change this in the following to the actual name of your UserForm:

1. Add the following into the code-behind of your UserForm (the Debug.Print is just to test the code works and you can remove it when it is, if you want to):

Sub CommandButtonMovement(cb As MSForms.CommandButton)
    FundNo = Right(cb.Name, 1)
    Debug.Print Now, FundNo
End Sub

2. Add a Class module (must be a Class module ... not standard module), name it CCommandButtonEvents, add this code

Option Explicit

Private WithEvents mCommandButton As MSForms.CommandButton
Private mUserForm As MyUserFormName

Sub SetUpCommandButton(cb As MSForms.CommandButton, uf As MyUserFormName)
    Set mCommandButton = cb
    Set mUserForm = uf
End Sub

Private Sub mCommandButton_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    mUserForm.CommandButtonMovement mCommandButton
End Sub

... remember to change "MyUserFormName" to the actual name of your UserForm.

3. Back in your UserForm code-behind, for each CommandButton you create, you need to add the following at the module level:

Dim cbe1 As CCommandButtonEvents
Dim cbe2 As CCommandButtonEvents

... as many as you are adding CommandButtons

4. Finally, assuming you are dynamically creating your CommandButtons in UserForm_Activate(), then add (where cb1, cb2 is assumed to be the names of the CommandButton ... change this as required) in that method:

Set cbe1 = New CCommandButtonEvents
cbe1.SetUpCommandButton cb1, Me

Set cbe2 = New CCommandButtonEvents
cbe2.SetUpCommandButton cb2, Me

... again, as many as you are adding CommandButtons

Run your code. That's a lot of changes ... I thought it might help to post a full code example:

1. Class CCommandButtonEvents - as above
2. UserForm MyUserFormName

Option Explicit

Dim FundNo As Variant

Dim cbe1 As CCommandButtonEvents
Dim cbe2 As CCommandButtonEvents

Private Sub UserForm_Activate()
    Dim cb1 As MSForms.CommandButton
    Set cb1 = Me.Controls.Add("Forms.CommandButton.1", "CommandButton1", True)
    cb1.Caption = "First button"
    cb1.Top = 20
    cb1.Left = 20
    Set cbe1 = New CCommandButtonEvents
    cbe1.SetUpCommandButton cb1, Me
        
    Dim cb2 As MSForms.CommandButton
    Set cb2 = Me.Controls.Add("Forms.CommandButton.1", "CommandButton2", True)
    cb2.Caption = "Second button"
    cb2.Top = 20
    cb2.Left = 100
    Set cbe2 = New CCommandButtonEvents
    cbe2.SetUpCommandButton cb2, Me
End Sub

Sub CommandButtonMovement(cb As MSForms.CommandButton)
    FundNo = Right(cb.Name, 1)
    Debug.Print Now, FundNo
End Sub

If you are adding your CommandButtons in a loop then the example code will need adjusting for that eg to use an Array instead of individual CCommandButtonEvents variables at point 3 etc

EDIT: updated code per the questioners comments, to handle adding CommandButtons in an array and to handle multiple events (MouseMove and Click)

The UserForm code-behind needs some fairly serious re-working though the core principles are the same ... this is the new code that includes an array to hold the CommandButtons and a loop to add them all (change the value of mBtnCount to the number of CommandButtons you want added ... ensure the UserForm is large enough that you can see them all!)

Option Explicit

Const mBtnCount As Long = 5

Dim FundNo As Variant
Dim mCmdBtns() As CCommandButtonEvents

Private Sub UserForm_Activate()
    Dim i As Long
    ReDim mCmdBtns(1 To mBtnCount)
    For i = 1 To mBtnCount
        Dim cmdBtn As MSForms.CommandButton
        Set cmdBtn = Me.Controls.Add("Forms.CommandButton.1", "CommandButton" & CStr(i), True)
        cmdBtn.Caption = "Button " & CStr(i)
        cmdBtn.Top = 4 + 26 * (i - 1)
        cmdBtn.Left = 4
        Set mCmdBtns(i) = New CCommandButtonEvents
        mCmdBtns(i).SetUpCommandButton cmdBtn, Me
    Next i
End Sub

Sub CommandButtonMovement(cb As MSForms.CommandButton)
    FundNo = Right(cb.Name, 1)
    Debug.Print Now, "Movement " & FundNo
End Sub

Sub CommandButtonClick(cb As MSForms.CommandButton)
    FundNo = Right(cb.Name, 1)
    Debug.Print Now, "Click " & FundNo
End Sub

... note there is a new Sub 'CommandButtonClick' that the code in CCommandButtonEvents will call.

Finally, to handle the Click event, in the CCommandButtonEvents Class module (you should normally use the drop-downs at the top of the code window to add the new event handlers to ensure the event 'signature' is correct), add this Sub which calls the new Sub in the UserForm:

Private Sub mCommandButton_Click()
    mUserForm.CommandButtonClick mCommandButton
End Sub