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?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

dataluver

Well-known Member
Joined
Jan 17, 2020
Messages
537
Office Version
  1. 365
Platform
  1. Windows
Here is one method. Using a collection, custom class, and a callback.
Post back if you have trouble adapting it to your project.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
Here is one method. Using a collection, custom class, and a callback.
Post back if you have trouble adapting it to your project.

Thanks for the link.
I will be glad if you can adapt it for me.
 

dataluver

Well-known Member
Joined
Jan 17, 2020
Messages
537
Office Version
  1. 365
Platform
  1. Windows
See file: Multiple Textboxes Single Event.xlsm in this folder.

This code in your userform:
VBA Code:
Option Explicit

Private c As New Collection

Private Sub UserForm_Initialize()
    Dim TBDoubleClick As CommonTextBoxDoubleClick, tb As MSForms.TextBox
    
    For Each tb In Controls
        If tb.Name Like "reg#*" Then
            Set TBDoubleClick = New CommonTextBoxDoubleClick
            TBDoubleClick.Init Me, tb
            c.Add TBDoubleClick
        End If
    Next
End Sub

Public Sub MultiTextBoxDoubleClick(tb As MSForms.TextBox)
    MsgBox "You double-clicked " & tb.Name
End Sub

This code in a class module named CommonTextBoxDoubleClick:
VBA Code:
Option Explicit

Private WithEvents tb As MSForms.TextBox
Private CallBackParent As Object

Friend Sub Init(caller As Object, o As MSForms.TextBox)
    Set tb = o
    Set CallBackParent = caller
End Sub

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

kelly mort

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

ADVERTISEMENT

See file: Multiple Textboxes Single Event.xlsm in this folder.

This code in your userform:
VBA Code:
Option Explicit

Private c As New Collection

Private Sub UserForm_Initialize()
    Dim TBDoubleClick As CommonTextBoxDoubleClick, tb As MSForms.TextBox
    
    For Each tb In Controls
        If tb.Name Like "reg#*" Then
            Set TBDoubleClick = New CommonTextBoxDoubleClick
            TBDoubleClick.Init Me, tb
            c.Add TBDoubleClick
        End If
    Next
End Sub

Public Sub MultiTextBoxDoubleClick(tb As MSForms.TextBox)
    MsgBox "You double-clicked " & tb.Name
End Sub

This code in a class module named CommonTextBoxDoubleClick:
VBA Code:
Option Explicit

Private WithEvents tb As MSForms.TextBox
Private CallBackParent As Object

Friend Sub Init(caller As Object, o As MSForms.TextBox)
    Set tb = o
    Set CallBackParent = caller
End Sub

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


You are too much!!!!!

Finally it's done - I have been thinking about this since 2016.

Thanks for it.
 

kelly mort

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

ADVERTISEMENT

Now that's funny! I'll see you in four years!

Don't got yet.
When I try the code in my real project, I get run-time error 13
Type mismatched
 

dataluver

Well-known Member
Joined
Jan 17, 2020
Messages
537
Office Version
  1. 365
Platform
  1. Windows
Please post your code or share your workbook at the link I posted earlier.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,796
Office Version
  1. 2016
Platform
  1. Windows
QUOTE="dataluver, post: 5568756, member: 456155"]
Please post your code or share your workbook at the link I posted earlier.
[/QUOTE]
Oh okay I think I know why I am getting the error message.

I have two comboboxes among the textboxes!!!


Which part of the code should I adjust?
 

dataluver

Well-known Member
Joined
Jan 17, 2020
Messages
537
Office Version
  1. 365
Platform
  1. Windows
I am hoping that this will work.
I updated the example file as well.

VBA Code:
Private Sub UserForm_Initialize()
    Dim TBDoubleClick As CommonTextBoxDoubleClick, ctrl As Control
    
    For Each ctrl In Controls
        If TypeName(ctrl) = "TextBox" Then
            If ctrl.Name Like "reg#*" Then
                Set TBDoubleClick = New CommonTextBoxDoubleClick
                TBDoubleClick.Init Me, ctrl
                c.Add TBDoubleClick
            End If
        End If
    Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,539
Messages
5,523,434
Members
409,522
Latest member
szophie

This Week's Hot Topics

Top