Passing argument from Userform to a code module

Hello -
I am writing VBA code in an Excel spreadsheet.  I would like to have the code in a module run to a certain point and then invoke a Userform that contains radio buttons.  I would like to have the user pick an option among the radio buttons and then click a command button that will either unload the Userform or hide it.  How do I pass the value from the selected radio button back into the code that is running in the module?  What I would like to do is have a series of if-then statements which would examine what radio button was selected, and then branch to different actions depending on the radio button selection.  To keep the project simple, I don't want to have the if-then statements reside inside the code for the Userform itself.
Thanks very much.
LeeBabAsked:
Who is Participating?
 
Patrick MatthewsCommented:
LeeBab,

Brief example:


Sub Foo()

    Dim Var1 As String
    Dim Var2 As Date
    Dim Var3 As Double

    'some code

    With MyUserForm
        .Show 'control now passes to UserForm
        'control has passed back to this sub because event code for the UF caused it to Hide
        Var1 = .ComboBox1
        Var2 = CDate(.Textbox1)
        Var3 = CDbl(.Textbox2)
    End With
    Unload MyUserForm

    'more code

End Sub



Regards,

Patrick
0
 
DaveCommented:
I typically use the UserForm.Tag property to hold a temporary choice  while the UserForm is hidden
I will pull together a simple example
Cheers
Dave
0
 
Patrick MatthewsCommented:
Hello LeeBab,

What I usually do in this situation is Hide the UserForm, which keeps it in memory but passes control back
to the original sub.  My original sub can then interrogate the UserForm controls for the values it needs.

If you do that, just be sure that you do eventually Unload the UserForm :)

Regards,

Patrick
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
borgunitCommented:
Something like:

Select case True
Case UserForm.RadioButton1.Checked
    DoWhatever1
Case UserForm.RadioButton2.Checked
    DoWhatever2
Case UserForm.RadioButton3.Checked
    DoWhatever3
End Select
0
 
DaveCommented:
LeeBab,
example below. If you had a lot of buttons then a class module would make it simpler to run additional buttons
Cheers
Dave
Hi Patrick

Option Explicit
 
Sub PickVal()
UserForm1.Show
MsgBox "User picked " & UserForm1.Tag
Unload UserForm1
End Sub
 
 
'UserForm1 Code
Private Sub OptionButton1_Click()
Me.Tag = "No 1"
Me.Hide
End Sub
 
Private Sub OptionButton2_Click()
Me.Tag = "No 2"
Me.Hide
End Sub
 
Private Sub OptionButton3_Click()
Me.Tag = "No 3"
Me.Hide
End Sub

Open in new window

uf.xls
0
 
GrahamSkanRetiredCommented:
You don't need extra variables &c. Just open the UserForm modally. Hide it instead of unloading it. Then the Form controls are still valid.


Module:
 
Sub CallUserForm1()
    UserForm1.Show vbModal
    Select Case True
        Case UserForm1.OptionButton1.Value
            MsgBox "Option1 was selected"
        Case UserForm1.OptionButton2.Value
            MsgBox "Option2 was selected"
    End Select
End Sub
 
Form:
 
Private Sub CommandButton1_Click()
    Me.Hide
End Sub

Open in new window

0
 
DaveCommented:
Graham,
Both Patrick and I already have suggested  hidng the form and returning the value directly.
But always looping through a number of controls  - potentially many - in the main code to find a value, is inefficient as opposed to immediately returning the value directly from the form
Regards
Dave
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.