excelvbaoffice365userform

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


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


Solution

  • 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!