Excel VBA - Assign value from UserForm ComboBox to Global Variable

Question

I have a userform with a basic combobox and command button. When the user hits the command button, I want the UserForm to close, and the value of the combobox to be saved in a variable that can be accessed by a subroutine contained within "ThisWorkbook".

In the UserForm code:

Public employee_position As String    
Public Sub CommandButton1_Click()

    employee_position = Me.ComboBox1.Value
    Unload Me

End Sub

In the "ThisWorkbook" Code

Private Sub GetUserFormValue()
    Call Userform_Initialize
    EmployeePosition.Show
    MsgBox employee_position
End Sub

When "GetUserFormValue()" runs, the UserForm comes up, you can select a value in the combobox and press the command button, but when the MsgBox comes up, it displays "" (Nothing)

What am I doing wrong here?

Put the global variable in the ThisWorkbook module, not in the form module. — Tim Williams
If I move the global variable to the ThisWorkboox module, the result is the same: employee_position = "" — P4U1
How about in a regular module? I get intellisense for the variable if I do that, but not if it's in ThisWorkbook. — Doug Glancy
What you really should be doing is declaring an instance of a userform and returning the choice to the calling routine, as described in Professional Excel Development. This post on my blog might be of interest: yoursumbuddy.com/a-flexible-vba-chooser-form — Doug Glancy
I just followed @DougGlancy 's link, and see that he essentially does the same thing I suggested in my answer: hide the form, process the variables in calling routine, unload. — Floris

Floris Floris · Accepted Answer · 2013-01-26T05:10:08

When you Unload Me, I think you lose all information associated with the module (including the global variable). But if you use Me.Hide rather than Me.Unload, then you can access the value of the form after the routine returns. So try this:

-- userform code includes:

Public Sub CommandButton1_Click()
    Me.Hide
End Sub

-- main module includes:

Private Sub GetUserFormValue()
    Call Userform_Initialize
    EmployeePosition.Show
    MsgBox EmployeePosition.ComboBox1.Value
    Unload EmployeePosition
End Sub

I think that should work.

Excel VBA - Assign value from UserForm ComboBox to Global Variable

2 Answers