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
– Tim Williams, Commented Jan 25, 2013 at 20:54
If I move the global variable to the ThisWorkboox module, the result is the same: employee_position = "" — P4U1
– P4U1, Commented Jan 25, 2013 at 20:57
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
– Doug Glancy, Commented Jan 25, 2013 at 21:08
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
– Doug Glancy, Commented Jan 25, 2013 at 21:13
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, Commented Jan 26, 2013 at 13:17

Floris · Accepted Answer · 2013-01-26 05:10:08Z

3

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.

answered Jan 26, 2013 at 5:10

Floris

46.6k7 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AndyUpNorth · Accepted Answer · 2014-05-02 13:45:01Z

2

I had the same problem, and this is how I resolved it:

If the main code is in a worksheet, and the variable is declared as public in that worksheet (e.g. in Microsoft Excel Objects -> Sheet1 (Sheet1)), the result from "Unload Me" cannot be passed from a UserForm to the worksheet code.

So to solve my problem, I inserted a new Module, and declared my public variable there. I didn't even have to move my code from the worksheet to the module... just the declaration of the public variable.

I hope this works for you too!

Andrew

answered May 2, 2014 at 13:45

AndyUpNorth

211 bronze badge

1 Comment

Joe Stellato Over a year ago

This is such an unobvious answer. Every time I would set my public variable from a UserForm it would empty itself. Adding it do workbook, or the sheet didn't make a difference. This worked perfectly.

Collectives™ on Stack Overflow

Excel VBA - Assign value from UserForm ComboBox to Global Variable

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related