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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,799
Office Version
  1. 2016
Platform
  1. Windows
This is the code that @mikerickson gave me when the controls were on multipage forms and frames.
Code:
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,799
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,939
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,799
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,545
Messages
5,529,458
Members
409,879
Latest member
Aussie_Excel_Wanna_Be
Top