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