Getting Data from a Userform

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

Sub UseFormData()
   
    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

Private Sub CommandButton1_Click()

    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.

userform with three checkboxes and a go button msgbox showing which checkboxes were checked
Posted in Uncategorized

13 thoughts on “Getting Data from a Userform

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

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

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

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

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

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

  8. 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?

  9. #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.

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

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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.