Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Option Buttons to choose which macros to run

  Asked By: Techguy    Date: Oct 09    Category: MS Office    Views: 4460
  

Everyone has been so helpful in the past, that I look forward to all the
responses to my
question. I created a userform in Excel VBE. When the user form is active I have
a few option
buttons that I need use for determining which macros to run. Each macro would be
active
when the respective option button is selected, then I would have a main control
button to run
the now selected macros. I have never used option buttons before and greatly
appreciate the
assistance.

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Viheke Fischer     Answered On: Oct 09

I take it you have something like:
macro  1
o Macro 2
o Macro 3
<Cancel> <Run>

In your properties panel for the option  buttons,
Make note of the Option button  names (or name them yourself)
and put a name in the GroupName section.
(use the same name for all of the option buttons)
This enables only ONE option button to be selected.
If Macro 1 is selected, and the user  selects
Macro 2, Macro 1 is automatically de-selected.

Next... in your "run" macro,
check the state of the selection by:

Userform.hide
'------------------------------
if (Userform.Opt_Macro1.value) then
'(run Macro associated with Macro 1)
elseif (Userform.Opt_Macro2.value) then
'(run Macro associated with Macro 2)
elseif (Userform.Opt_Macro3.value) then
'(run Macro associated with Macro 3)
end if
'------------------------------
Unload Userform

Note:
I presume that since you're not used to using Option buttons,
you might not be familiar with the difference between
hiding a userform  and unloading it.

Once a user hits the "run" button, I don't want the userform on
the screen while the macro is running. (I've got better things to see!)

But if in the "run" macro, I UNLOAD the userform. All of it's
values and selections are "unloaded" from memory and no longer
available to VBA!.. So that means that if it isn't on the screen,
my macro can't check to see what entries the user is made.

Say, my userform has a textbox that the user enters a "document" number.
I also have option buttons  so that the user can select to View, Edit or Print
the document. He makes his selections/entries and hits "OK".
My macro kicks off and the first thing it does is unload the form.
Then it checks to see what the user entered/selected.. but nothing is there!!!
So...

When a userform is "hidden" it still is loaded into memory, and
the values associated with the selections on the form  are still
available to VBA.

It's still a good idea to "unload" the form when you're done with it though.
Unless you want to be able to bring it up and have the userform show
what the last entries were...
Userform.Show would bring it back up with all of the last selections intact.
Be careful though. If you "hide" it, then "show" it, the Initialize subroutine
for the userform doesn't execute for each subsequent ".Show"...
Which makes sense... the first time your ".show" it, it is "Initialized".
after that, you're just displaying and hiding it, not "starting" it each time.

hope this helps without being too much of:
"I already know all of that, I just had a simple question! I didn't need a
book!"

 
Answer #2    Answered By: Jeanette Greene     Answered On: Oct 09

Even if the original
questioner doesn't need all the explanation, there will surely be some
readers here who do need it. I appreciate the time you took to post
such a clear answer.

 
Answer #3    Answered By: Isabella Campbell     Answered On: Oct 09

Thank you for your response. I now realizing that maybe option  buttons aren't
the best way for me to handle what trying to do. I have the three macros  and I
need to be able to select any of the three, two of the three or all of them. The
macros help execute some project modeling where I have three job codes and nine
scenarios for each job code. I knew that once I had the code for the jobs the
scenerios would be the same. Should I be using check boxes instead and any
suggestions there? Again thank you for your help.

 
Answer #4    Answered By: Logan Bouchard     Answered On: Oct 09

Sorry... I guess my explanation WAS too verbose.
If you want to allow multiple selections, that's easy to do.
Just DON'T use a GroupName.
Then each option  button is independent.
then the code is like:

Userform.hide
'----------- --------- --------- -
if (Userform.Opt_ Macro1.value) then
'(run Macro associated with Macro 1)
end if
if (Userform.Opt_ Macro2.value) then
'(run Macro associated with Macro 2)
end if
if (Userform.Opt_ Macro3.value) then
'(run Macro associated with Macro 3)
end if
'----------- --------- --------- -
Unload Userform

 
Answer #5    Answered By: Aidyn Smith     Answered On: Oct 09

Here is the code I am goin to try. Is this correct?
Private Sub RunModel1_Click()
ModelSelect.Hide
If ModelSelect.OptionCAM.Value Then
Call CAMModel
End If

If ModelSelect.OptionMGR.Value Then
Call MGRModel
End If

If ModelSelect.OptionDIR.Value Then
Call DIRModel
End If

End Sub

 
Answer #6    Answered By: Abbad Akhtar     Answered On: Oct 09

I tried the code WITHOUT a GroupName, but when I show the UserForm to choose  the
macros I want to run  I can only choose one option. When I select and additional
macro the first option  becomes "False."
Any suggestions?

 
Answer #7    Answered By: Cais Nguyen     Answered On: Oct 09

yeah, I forgot about that.
Here are your options:
the Userform automatically assumes
that any option  buttons in a single "frame" are part
of a group, even though the group isn't named.
You can put each button  in a separate 'frame'
and change the border color to the same as your background,
remove the frame name.
or... user  Checkboxes instead of option buttons.
BTW: your code looks right.

 
Didn't find what you were looking for? Find more on Option Buttons to choose which macros to run Or get search suggestion and latest updates.




Tagged: