Difference between running a userform in Visual Basic Editor (F5) and opening it with Show?

Question

I have a userform (form_Updaterow), putting together some scripts to show additional info in a label when a textbox is clicked on.

It works when I F5-run the userform. When I open the form using .Show (either from another form or 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) 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):

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. (Because some of the textboxes are in frames, I had to add the activename() function. 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

When I run the form (form_Updaterow) using F5 the code works: I get my "UserForm initialized" message and then a pop-up with the name of the clicked-upon text box (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.

However, when I load the form using .Show, for example using the macro below (also when I load from another form): 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, and then multiple empty message boxes.

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

The problem seems to be in identifying the ActiveControl.

What 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 loading my form.

I am using Excel on Office 365, ,Version 2408 (Build 17928.20156 Click-to-Run) and VBA 7.1.1143

Answer 1

Thanks to @TimWilliams for providing the solution for the initial problem. Instead of calling TheInfoProvider and having it find the selected TextBox using a loop, passing it the TextBox directly solved that issue. Another problem came up when updating the guidance label, though, which got solved after @TimWilliams suggested using .Parent (with a little tweak in case of textboxes that were in a frame).

Here is the new, fully working code:

In the clsTextBox class module

Private Sub MyTextBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    TheInfoProvider MyTextBox
End Sub

In the mod_Infos module (I commented out the old code to show the difference)

Public Sub TheInfoProvider(c)
'MsgBox ("I'm the Info Provider")
'    For Each c In form_Updaterow.Controls
'
'        If TypeName(c) = "TextBox" Or TypeName(c) = "ComboBox" Then
'            MsgBox cargo.Name
'            If c.Name = cargo.Name Then
        Dim ancestry As Object
        Set ancestry = c.Parent
        If c.Parent.Name <> "form_Updaterow" Then
            Set ancestry = c.Parent.Parent
        End If
        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)
        
        ancestry.fr_varname.Visible = True
        ancestry.lbl_varname.Caption = varname
        
        guidance = DatDic.ListColumns("Guidance").Range.Cells(tabrow)
        ancestry.lbl_guidance.Caption = guidance
'            End If
'
'        End If
'    Next c
End Sub

Thanks so much for helping me out with this!