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]

Okay cool.

Currently, it would be just changing the text of textboxes and selecting items from the comboboxes.

But all these are done after the double click event you created for me.

 

[Jaafar Tribak]

One last thing!

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

Sinking Enter,Exit,BeforeUpdate and AfterUpdate events in a class module requires the shell API function: ConnectToConnectionPoint

Alternatively, (easier than the API method) you can use the following hack for sinking the Enter and Exit events of all controls :

In the UserForm Module:

Option Explicit

Private Sub UserForm_Activate()

    'run any existing activate-event code here before sinking events...
    
    SinkEnterExitEvents = True
End Sub

Private Sub UserForm_Terminate()
    SinkEnterExitEvents = False
End Sub

Private Sub OnEnterControl(ByVal Ctrl As Control)
    With Ctrl
        Debug.Print "Entered Control: " & .Name
        .Tag = .BackColor
        .BackColor = vbYellow
     End With
End Sub

Private Sub OnExitControl(ByVal Ctrl As Control)
    With Ctrl
        Debug.Print "Exited Control: " & .Name
        .BackColor = .Tag
    End With
End Sub

Private Property Let SinkEnterExitEvents(ByVal SinkEvents As Boolean)

    Const STATE_SYSTEM_FOCUSED = &H4
    Const STATE_SYSTEM_SELECTABLE = &H200000
    
    Static bEventSinking As Boolean
    Dim oCtrl As Control, oActiveControl As Control, oPrevActiveControl As Control, iAcc As IAccessible

    bEventSinking = SinkEvents
    
    Do While bEventSinking
        For Each oCtrl In Me.Controls
            Set iAcc = oCtrl
            If iAcc.accState(0&) And (STATE_SYSTEM_FOCUSED Or STATE_SYSTEM_SELECTABLE) Then
                Set oActiveControl = oCtrl
                If Not (oPrevActiveControl Is oActiveControl Or oPrevActiveControl Is Nothing) Xor oPrevActiveControl Is Nothing Then
                    If Not oPrevActiveControl Is Nothing Then
                        Call OnExitControl(oPrevActiveControl)
                    End If
                    Call OnEnterControl(oCtrl)
                End If
            End If
        Next
        Set oPrevActiveControl = oActiveControl
        DoEvents
    Loop

End Property

 

[kelly mort]

Hello @Jaafar Tribak
The code is wonderful and it's working fine. The only issues are:
1. It takes very long for the userform to close.
2. I only need it to work for a group of controls - reg2 to reg20.
Reg3 and reg4 are comboboxes while the rest are textboxes.

Thanks again.