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?

 

[Legacy 456155]

Here is one method. Using a collection, custom class, and a callback.

VBA Userform with multiple combobox but same options

Hi, I am working on something that requires multiple combobox. Some have unique options but most only require a yes/no option. Is there a way to add that option from combobox10 to combobox 30? Thank you!

www.mrexcel.com

Post back if you have trouble adapting it to your project.

 

[kelly mort]

Here is one method. Using a collection, custom class, and a callback.

VBA Userform with multiple combobox but same options

Hi, I am working on something that requires multiple combobox. Some have unique options but most only require a yes/no option. Is there a way to add that option from combobox10 to combobox 30? Thank you!

www.mrexcel.com

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.

 

[Legacy 456155]

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

This code in your userform:

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:

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]

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

This code in your userform:

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:

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.

 

[Legacy 456155]

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

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

 

[kelly mort]

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

 

[Legacy 456155]

Please post your code or share your workbook at the link I posted earlier.

 

[kelly mort]

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?

 

[Legacy 456155]

I am hoping that this will work.
I updated the example file as well.

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