Bill asks what the best way to get data from a userform is: class module? global variables? I’m not a fan of global variables because it’s not scalable. That is, if I add a new control, I have to add a new variable. That’s too much maintenance.
So it’s class module, then? Well, yes and no. I do use a class module, but I don’t create a new one with a bunch of Property Get and Let statements. Remember that the userform is itself a class module. All you need to do is keep the variable that refers to your userform in scope and you can refer to the controls all you want. You get a bunch of built-in Gets and Lets. Here’s an example
Dim ufForm1 As UserForm1
Dim sMsg As String
Dim ctl As Control
Set ufForm1 = New UserForm1
ufForm1.Show
sMsg = “You selected “ & vbNewLine & vbNewLine
For Each ctl In ufForm1.Controls
If TypeName(ctl) = “CheckBox” Then
If ctl.Value Then
sMsg = sMsg & vbTab & ctl.Name & vbNewLine
End If
End If
Next ctl
MsgBox sMsg
Unload ufForm1
End Sub
Behind the userform’s Go button, I have
Me.Hide
End Sub
Hiding the form causes UseFormData to continue execution right after the Show line. I can use my ufForm1 variable to access the controls on the form. ufForm1 can also be passed to called procedures, so you don’t need to keep everything in one sub.
You could use Unload Me behind the Go button, but I tend to use Hide. Since the variable ufForm1 still refers to the class, you can actually unload it. It has the same effect, Hide just seems clearer.
If you need your values to persist longer than the current sub, you can change the scope of ufForm1 to module level or global.
 |
 |
Dick: Maybe I’m missing something, but I don’t see the point of the exercise. As long as you are hiding the UserForm rather than unloading it, I think you can get the same results and the same access to the UserForm’s controls by using a static UserForm, without having to use the ufForm1 variable.
But it’s late here on the East Coast and I’ve spent a few hours in my neighborhood bar, so I probably don’t know what I’m talking about :).
That’s right. If I don’t use a variable, then someone will say “Didn’t you read Professional Excel Development?”. Chapter 10 says never use the default instance. However you refer to the userform, the point is that if you keep it in scope, you don’t need to transfer the data to something else, like global variables. Now I need to catch up on my drinking.
Aha … that explains it. I’m not upto Chapter 10 yet! ;-)
Dick –
You’re still better off using all the Gets and Lets in the UserForm. This insulates the rest of the code from changes to the form’s design.
Say I want to change from checkboxes to a multiselect listbox. If I refer directly to the form’s checkboxes, I have to hunt down all the places I refer to them, and change to whatever’s appropriate for the listbox.
Using Lets and Gets, for example, I can pass an array of trues and falses for what’s selected and not selected, then loop through this array and check the checkbox or highlight the listbox entry within the form’s code, and the calling code doesn’t have to understand any of this.
That’s the whole reasoning behind the class module, to hide the code behind a simple interface. And Chapter 10 of Professional Excel Development says to “expose properties and methods, not controls”, right after “use classes, not the default instance”.
– Jon
#4 how does the program know which controls the array of trues and falses refers to?
Hi Bill
The point is that the code outside the form shouldn’t know which controls the Trues and Falses refer to. In practice, you wouldn’t just return an array of True/False, but rather a list of selected ID, say, or an array of the text of the selected items. So the code in the form would translate the UI shown to the user into an array of IDs or codes etc that would be exposed to the rest of the program. By doing that, you’re free to change the design of the form all you like, safe in the knowledge that you’re not breaking any calling code.
Regards
Stephen Bullen
Bill –
Stephen explained it nicely. My thought was incomplete; what I often do in practice is send a two-column array. Column 1 identifies some item, and column 2 identifies a value (true/false, an index, whatever). The form’s code reads the list, says, “Aha, item 1 is true,” then highlights the item in the listbox, or checks its checkbox, or whatever.
– Jon
Why don’t you return the data using a function with an argument thats passed by reference?
The function is just a wrapper around the Show-method of the userform.
Option Explicit
Option Base 0
Attribute VB_Name = “GetDataUserForm”
Private priv_userform_result As VbMsgBoxResult
Private Sub OkButton_Click()
On Error Resume Next
priv_userform_result = vbOK
Me.Hide
End Sub
Private Sub OkButton_Click()
On Error Resume Next
priv_userform_result = vbCancel
Me.Hide
End Sub
‘ Prevents unload of userform if the user
‘ clicks on the upper-right Close button
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
priv_userform_result = vbCancel
Cancel = 1
Me.Hide
End If
End Sub
Public Function ShowResult(ByRef selected_ids() as Integer, ByRef nbr_ids as Integer) as VbMsgBoxResult
On Error Resume Next
Dim ctl As Control
Dim i as Integer
i = 1
‘ set id for checkboxes
For Each ctl In Me.Controls
If TypeName(ctl) = “CheckBox” Then
ctl.Value = False
ctl.Tag = CStr(i)
i = i + 1
End If
Next ctl
priv_userform_result = vbCancel
nbr_ids = 0
Me.Show
‘ the execution continues if the user clicks
‘ Ok, Cancel or Close (the userforms is hidden)
‘ check the value of priv_userform_result
If priv_userform_result = vbOK Then
‘ which ids are selected ?
For Each ctl In Me.Controls
If TypeName(ctl) = “CheckBox” Then
If ctl.Value Then
nbr_ids = nbr_ids + 1
ReDim Preserve selected_ids(nbr_ids)
selected_ids(nbr_ids – 1) = CInt(ctl.Tag)
End If
End If
Next ctl
End If
ShowResult = priv_userform_result
Unload Me
End Function
—
In a CodeModule
Dim selected_ids() as Integer
Dim nbr_ids as Integer
If GetDataUserForm.ShowResult(selected_ids, nbr_ids) = vbOK Then
Dim i as Integer
For i = 0 to (nbr_ids – 1)
Debug.Print CStr(selected_ids(i))
Next
End If
Maybe I’m still missing something here. If I have a form and I want to get the values off the controls on that form into a standard module procedure.
No matter what if I add, delete, or change a control on the form I’m going to have to manually update code somewhere.
Right now I have a “shell” where users in my department can insert their own module and own user form. Then I have a procedure as part of my shell that will pass a two-dimensional array (containing control names and values) to their primary procedure in the standard module they inserted.
That way they know whatever they named the control on the form will be value in the array to search to find their value. Is there a better way for them to reference their values coming from the form?
To Billkamm
Do you want an userform which contains dynamically created checkboxes (or other controls).
The number of checkboxes and the names and values of these checkboxes is based on the number of values and the names and values of a two-dimensional array?
And the end you want to return an array with contains the names and values selected by the user?
#10
I loop through all the controls on the form to save/load defaults. The form also has a multi-page where each page is a department. When you call that department macro I need to pass the values of the controls to the primary procedure used to control all the macros for that department.
I want to make it so new modules can be inserted and all the user has to do is link up the values of the controls they created on a new page to their macro.
Dick mentioned:
ufForm1 can also be passed to called procedures, so you don’t need to keep everything in one sub.
However, when I try to pass the userform to a procedure, I get a type mismatch error. What am I doing incorrectly here:
Set frmA1 = New UserForm1
frmA1.Show
If frmA1.OK Then
Read_Settings frmA1
End If
I’m sorry, but I found my error. I did not have frmA1 declared. When I add this declaration (i.e., Dim frmA1 as UserForm1), it works fine.