and thank you in advance for your time,
I have been working on a userform (form_Updaterow), putting together some scripts to show additional info in a label when a textbox is clicked on. After much tinkering (I’m new to VBA, so I was not always sure of what I was doing) I finally managed to make it work. At least, it works fine when I F5-run the userform. However, when I open the form using .Show (either from another form or straight from a Macro), the script does not work. Could running and showing make something different?

This is what I am (in theory) doing:

1. In the form’s (form_Updaterow) code I define a new collection for TextBoxes when initialising the form (I added a MsgBox to confirm it runs this code)
   `Dim tbCollection As Collection
   Dim cbCollection As Collection

Private Sub UserForm_Initialize()
MsgBox (“UserForm initialized”)
Sheets(“DES”).Activate
Dim ctrl As MSForms.Control

Dim obj_tb As clsTextBox
Set tbCollection = New Collection
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.TextBox Then
            Set obj_tb = New clsTextBox
            Set obj_tb.Control = ctrl
            tbCollection.Add obj_tb
        End If
    Next ctrl
Set obj_tb = Nothing

End Sub`

2. In a class module (clsTextBox) I add the following code:
   `Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set Control(tb As MSForms.TextBox)
‘MsgBox (“TextBox property set”)
Set MyTextBox = tb
End Property

Private Sub MyTextBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
‘MsgBox (“Call the provider”)
Call TheInfoProvider
End Sub`

3. In a module (mod_Infos) I loop through my textboxes and identify the one that has been clicked on using this code (also, because some of the textboxes are in frames, I had to add the activename() function. Seems a bit sloppy on my part, but I couldn’t make it work otherwise). I then use the textbox’s name (‘txt_VARNAME’) to identify the guidance text in a table (DatDic_DES).
   `Public Sub TheInfoProvider()
   ‘MsgBox (“I’m the Info Provider”)
   For Each c In form_Updaterow.Controls

    If TypeName(c) = "TextBox" Or TypeName(c) = "ComboBox" Then
        MsgBox activename
        If c.Name = activename() Then
   
            varname = Split(c.Name, "_", 2)(1)
   
            Set DatDic = Worksheets("Data Dictionary").ListObjects("DatDic_DES")
            varnames = DatDic.ListColumns("Variable Name").Range
            tabrow = Application.Match(varname, varnames, 0)
   
            form_Updaterow.fr_varname.Visible = True
            form_Updaterow.lbl_varname.Caption = varname
   
            guidance = DatDic.ListColumns("Guidance").Range.Cells(tabrow)
            form_Updaterow.lbl_guidance.Caption = guidance
        End If
   
    End If
   

   Next c
   End Sub

Public Function activename() As String ‘MSForms.Control
Set ReallyActiveControl = form_Updaterow.ActiveControl
On Error Resume Next
Set ReallyActiveControl = ReallyActiveControl.ActiveControl
activename = ReallyActiveControl.Name
End Function
Now, when I run the form (form_Updaterow) using F5 the code runs nicely: I get my "UserForm initialized" message and then a pop-up with the name of the clicked-upon text box (in fact multiple times, once for each textbox in my form). The result is that the label (lbl_guidance`) now has changed to the guidance text for that textbox (neat!)

However, when I load the form using .Show, for example using the Macro below (also trying loading from another form), this is what happens: Before showing the form, I get my “UserForm initialized” message (so far, so good). Then, when I click on a textbox I get the same message again (weird, but ok…) and then multiple empty message boxes. So the problem seems to be in identifying the ActiveControl. However, what confuses me to no end (and makes trouble-shooting so challenging) is that the behaviour is different when I F5-run the form and when I Show the form using a macro (or button in another userform). I think it would help to understand the fundamental difference between one way and another of leading my form.

Public Sub Main() Dim frm As New form_Updaterow frm.Show vbModel Set frm = Nothing End Sub

Any ideas of what is going on?
Thanks!