How to locate an active control on a userform (reloaded) - vba

[kelly mort]

This is the code that @mikerickson gave me when the controls were on multipage forms and frames.

Public Function ReallyActiveControl(Optional Container As Object) As MSForms.Control
    If Container Is Nothing Then
        Set Container = Me
    End If
   
    On Error Resume Next
    Set ReallyActiveControl = Container.ActiveControl
    Set ReallyActiveControl = Container
    On Error GoTo 0
   
    On Error Resume Next
    Do
        If TypeName(ReallyActiveControl) = "MultiPage" Then
            With ReallyActiveControl
                Set ReallyActiveControl = .Pages(.Value)
            End With
        End If
        Set ReallyActiveControl = ReallyActiveControl.ActiveControl
    Loop Until Err
    On Error Goto 0
End Function

It didn't get me to the controls due to some reasons I can't tell.

Now my new demand is that I have removed all the multipages and frames. So the controls are placed directly on the parent form - UserForm.

What I want to achieve is to be able to identify the textbox that I will double click.

I have named the textboxes as reg1 reg2 and so on.

So when I double click reg2, my goal is to run a certain macro.
For example, I unlock reg2, change its background color and so on.
I could do it individually but the code is a bit lengthy and I am looking for a smart way to get around it.

Can someone show me how to fix that?

 

[kelly mort]

Sure it worked.

But what if I want to add the comboboxes to the double click events?

Like when I double click the comboboxes, the same thing happening for the textboxes should also happen.

 

[Legacy 456155]

That question is for 2024.

 

[Legacy 456155]

Are your comboboxes named "reg*" just like your textboxes?

 

[kelly mort]

Are your comboboxes named "reg*" just like your textboxes?

Yes they are also Reg*

 

[Legacy 456155]

Example file was updated.

Userform

Option Explicit

Private c As New Collection

Private Sub UserForm_Initialize()
    Dim CDC As CommonDoubleClick, ctrl As Control
    
    For Each ctrl In Controls
        If ctrl.Name Like "reg*" Then
            Set CDC = New CommonDoubleClick
            CDC.Init Me, ctrl
            c.Add CDC
        End If
    Next
End Sub

Public Sub CommonDoubleClick(ctrl As MSForms.Control)
    MsgBox "You double-clicked " & ctrl.Name
End Sub

Class module named CommonDoubleClick

Option Explicit

Private WithEvents tb As MSForms.TextBox
Private WithEvents cb As MSForms.ComboBox

Private CallBackParent As Object

Friend Sub Init(caller As Object, ctrl As MSForms.Control)
    Select Case TypeName(ctrl)
        Case "TextBox"
            Set tb = ctrl
        Case "ComboBox"
            Set cb = ctrl
    End Select
    Set CallBackParent = caller
End Sub

Private Sub tb_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    CallBackParent.CommonDoubleClick tb
End Sub

Private Sub cb_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    CallBackParent.CommonDoubleClick cb
End Sub

 

[kelly mort]

Great.

But when I set the combobox style to "DropDownList", the double click fails to respond.

It works when I set it to "DropDownCombo" .

But my dealings restrict me to set it to list instead of combo.

Can this too be fixed?

 

[Legacy 456155]

I suppose that the double-lick event is not fired when using that liststyle. I did not know that. You can create your own pseudo double-click event like this:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function GetDoubleClickTime Lib "user32" () As Integer
#Else
    Private Declare Function GetDoubleClickTime Lib "user32" () As Integer
#End If

Private WithEvents tb As MSForms.TextBox
Private WithEvents cb As MSForms.ComboBox

Private CallBackParent As Object
Private DoubleClickTime As Single

Friend Sub Init(caller As Object, ctrl As MSForms.Control)
    Select Case TypeName(ctrl)
        Case "TextBox"
            Set tb = ctrl
        Case "ComboBox"
            Set cb = ctrl
    End Select
    Set CallBackParent = caller
    DoubleClickTime = GetDoubleClickTime / 1000
End Sub

Private Sub cb_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Static PreviousTimer As Single, ClickTime As Single
    If PreviousTimer = 0 Or Abs(Timer - PreviousTimer) > DoubleClickTime Then
        PreviousTimer = Timer
    Else
        ClickTime = Abs(Timer - PreviousTimer)
        If ClickTime <= DoubleClickTime Then CallBackParent.CommonDoubleClick cb
        PreviousTimer = 0
    End If
End Sub

Private Sub tb_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    CallBackParent.CommonDoubleClick tb
End Sub

Private Sub cb_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    CallBackParent.CommonDoubleClick cb
End Sub

I updated the example.

 

[kelly mort]

Wow!!!

Excel is indeed powerful! !!!

Thanks for the fix .

I think you can go now - see you in 2024. Lol

 

[kelly mort]

One last thing!

With the same controls, how to I get the one which I just left?

Like the AfterUpdate event.

 

[Legacy 456155]

It depends. If setting focus is the only action that is available to determine which control has the focus, you will need to trap the control's enter or exit event. This is not a straight-forward process from my experience. If other user actions are required or expected, such as selecting something from a list, double-clicking, or changing text, you may be able to use another event to save info about the active control to a variable and then use it later.