Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Techguy Sr eng.   on Oct 09 In MS Office Category.

  
Question Answered By: Viheke Fischer   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!"

Share: 

 

This Question has 6 more answer(s). View Complete Question Thread

 
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: