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.)

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<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
</code>
<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 </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):

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<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
</code>
<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 </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. (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).

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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
</code>
<code>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 </code>
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.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>Public Sub Main()
Dim frm As New form_Updaterow
frm.Show vbModel
Set frm = Nothing
End Sub
</code>
<code>Public Sub Main() Dim frm As New form_Updaterow frm.Show vbModel Set frm = Nothing End Sub </code>
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

New contributor

Alexander Jarde is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

9

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>Private Sub MyTextBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
TheInfoProvider MyTextBox
End Sub
</code>
<code>Private Sub MyTextBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) TheInfoProvider MyTextBox End Sub </code>
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)

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
<code>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
</code>
<code>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 </code>
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!

New contributor

Alexander Jarde is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

4

Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa Dịch vụ tổ chức sự kiện 5 sao Thông tin về chúng tôi Dịch vụ sinh nhật bé trai Dịch vụ sinh nhật bé gái Sự kiện trọn gói Các tiết mục giải trí Dịch vụ bổ trợ Tiệc cưới sang trọng Dịch vụ khai trương Tư vấn tổ chức sự kiện Hình ảnh sự kiện Cập nhật tin tức Liên hệ ngay Thuê chú hề chuyên nghiệp Tiệc tất niên cho công ty Trang trí tiệc cuối năm Tiệc tất niên độc đáo Sinh nhật bé Hải Đăng Sinh nhật đáng yêu bé Khánh Vân Sinh nhật sang trọng Bích Ngân Tiệc sinh nhật bé Thanh Trang Dịch vụ ông già Noel Xiếc thú vui nhộn Biểu diễn xiếc quay đĩa Dịch vụ tổ chức tiệc uy tín Khám phá dịch vụ của chúng tôi Tiệc sinh nhật cho bé trai Trang trí tiệc cho bé gái Gói sự kiện chuyên nghiệp Chương trình giải trí hấp dẫn Dịch vụ hỗ trợ sự kiện Trang trí tiệc cưới đẹp Khởi đầu thành công với khai trương Chuyên gia tư vấn sự kiện Xem ảnh các sự kiện đẹp Tin mới về sự kiện Kết nối với đội ngũ chuyên gia Chú hề vui nhộn cho tiệc sinh nhật Ý tưởng tiệc cuối năm Tất niên độc đáo Trang trí tiệc hiện đại Tổ chức sinh nhật cho Hải Đăng Sinh nhật độc quyền Khánh Vân Phong cách tiệc Bích Ngân Trang trí tiệc bé Thanh Trang Thuê dịch vụ ông già Noel chuyên nghiệp Xem xiếc khỉ đặc sắc Xiếc quay đĩa thú vị
Trang chủ Giới thiệu Sinh nhật bé trai Sinh nhật bé gái Tổ chức sự kiện Biểu diễn giải trí Dịch vụ khác Trang trí tiệc cưới Tổ chức khai trương Tư vấn dịch vụ Thư viện ảnh Tin tức - sự kiện Liên hệ Chú hề sinh nhật Trang trí YEAR END PARTY công ty Trang trí tất niên cuối năm Trang trí tất niên xu hướng mới nhất Trang trí sinh nhật bé trai Hải Đăng Trang trí sinh nhật bé Khánh Vân Trang trí sinh nhật Bích Ngân Trang trí sinh nhật bé Thanh Trang Thuê ông già Noel phát quà Biểu diễn xiếc khỉ Xiếc quay đĩa
Thiết kế website Thiết kế website Thiết kế website Cách kháng tài khoản quảng cáo Mua bán Fanpage Facebook Dịch vụ SEO Tổ chức sinh nhật