Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
Put the global variable in the ThisWorkbook module, not in the form module. – Tim Williams Jan 25 at 20:54
If I move the global variable to the ThisWorkboox module, the result is the same: employee_position = "" – P4U1 Jan 25 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 Jan 25 at 21:08
1  
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 Jan 25 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 Jan 26 at 13:17

1 Answer

up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.