?
Solved

Passing argument from Userform to a code module

Posted on 2009-02-13
7
Medium Priority
?
988 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:LeeBab
7 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 23632343
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 23632347
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 300 total points
ID: 23632371
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 10

Assisted Solution

by:borgunit
borgunit earned 100 total points
ID: 23632410
Something like:

Select case True
Case UserForm.RadioButton1.Checked
    DoWhatever1
Case UserForm.RadioButton2.Checked
    DoWhatever2
Case UserForm.RadioButton3.Checked
    DoWhatever3
End Select
0
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 300 total points
ID: 23632420
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
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 300 total points
ID: 23635258
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 23637067
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question