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

kelly mort

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

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
L

Legacy 456155

Guest

ADVERTISEMENT

Example file was updated.

Userform
VBA Code:
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
VBA Code:
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

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

Legacy 456155

Guest

ADVERTISEMENT

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:
VBA Code:
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

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
Wow!!!

Excel is indeed powerful! !!!

Thanks for the fix .

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

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
One last thing!

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

Like the AfterUpdate event.
 
L

Legacy 456155

Guest
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,406
Messages
5,528,591
Members
409,827
Latest member
Tmcgrew05

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top