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
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
Behind the userform’s Go button, I have
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.