Option box for running a macro
I have answered my own question...
userform1.hide - of course!
"Ant" wrote:
Hi Carlo. I have it working now (although it didn't like the Me bits for some
reason). I added a commandbutton called "GO" on the userform and once the
selection was made and the GO button pushed it ran the relative Macro. The
initial Macro button had the UserForm.Show. I tried removing this as you
mentioned but it seemed to require it. The call code sits under the
commandbutton and the main code in seperate modules.
One question though...How do you make the userform close once the Macro has
started?
Thanks for your help. Ant.
"carlo" wrote:
I forgot something:
'-------------------------------------------------------------------------
Private Sub CommandButton1_Click()
If Me.OptionButton1.Value = True Then
Call Macro1
Else
Call Macro2
End If
Unload Me
End Sub
'-------------------------------------------------------------------------
the Me is usually needed, to tell Excel where the Button is located
cheers Carlo
On Nov 28, 11:48 am, carlo wrote:
I don't recommend to put the code in Userform_Click,
it triggers also, if the user clicks somewhere else on
the form.
Make a Button, that's easier to understand. And put
the Code directly on the button.
To connect the two optionbuttons you have to put
them into a frame control. that way only one of the
optionbuttons can be true.
Your sub looks actually fine, so I don't know why
it doesn't work.
I changed minor details:
'-------------------------------------------------------------------------
Private Sub CommandButton1_Click()
If OptionButton1.Value = True Then
Call Macro1
Else
Call Macro2
End If
Unload Me
End Sub
'-------------------------------------------------------------------------
(you don't need the UserForm.show, because the
form is already visible)
Make the option1 default true, that way you prevent
the user from clicking the button without selecting
anything.
Give me some feedback if it works or not.
hth
Carlo
On Nov 28, 11:04 am, Ant wrote:
I am almost there... I have created the UserForm with two option buttons for
the 2 macros. I seem to be missing the code which connects the selection of
the option button and running of either of the macros. At the moment the
UserForm comes up on screen, I select an option then the macro just stops.
Can you see what I am missing? Also, I'm not sure where I should put the
code. Do I have it within the UserForm as below, or should it be seperate?
Private Sub UserForm_Click()
UserForm1.Show
If OptionButton1.Value = True Then
Call Macro1
ElseIf OptionButton2.Value = True Then
Call Macro2
Else
Exit Sub
End If
End Sub
"carlo" wrote:
In that case you should consider to build your own userform.
(which sounds harder than it is.)
You should find some nice tutorials online.
If you have any problems, just ask.
Cheers
Carlo
On Nov 28, 5:14 am, Ant wrote:
Thanks for your reply. Do you know if it is possible to have a box with the
selection built in? Perhaps a drop down arrow with both choices available.
This would allow the user to simply select an option without having to type
in anything.
"JLGWhiz" wrote:
The simplest method would be the input box.
Sub opt1or2()
myOpt = Application.InputBox("Enter either a 1 or a 2.", "Pick Option",
Type:=1)
If myOpt = 1 Then
Call Macro1
ElseIf myOpt = 2 Then
Call Macro2
Else
Exit Sub
End If
End Sub
You can attach this to a button from the forms tool bar or the ToolBox tool
bar. See VBA help for using the ToolBox button.
"Ant" wrote:
I have 2 macros which I would like to combine into one. Basically I would
like the user to start the macro (push a button) and a window/balloon or
something will pop up with the choice of option 1 or option 2. Choosing
option 1 will run macro 1 and choosing option 2 will run macro 2.
Thanks in advance.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
|