Option Buttons and Combo Bars

[A Mad Doberman]

I need to see some example code from some of the guru's out there...I
learn pretty fast from example.

What I'm trying to do is gather info from the user of a spreadsheet,
while a macro is running, by having the user select an appropriate
option via an option button or combo box. I have seen a lot of posts
related to option buttons and combo box's that exist within the
spreadsheet, but have found nothing on using option buttons/combo
boxes that exist only within the running macro for the purposes of
gathering input data.

Can anyone point me to some code (or post a simply example) that will
employee an option button or combo box within the macro, gather the
neccessary data, and store the data to a variable?

Thanks in advance!

 

[Susan]

warning, i'm not a guru............
what about having a userform pop up at the appropriate time in the
coding to have the user make his choices? you could either already
have it set up & just load it at the right time, or build it
programmatically (waaaaaay beyond me). then just hide it to keep the
values for the variables until you're ready to unload it.
just an idea.
susan

 

[Office_Novice]

Sounds like you need a user Form check this out.

http://www.contextures.com/xlUserForm02.html

 

[A Mad Doberman]

warning, i'm not a guru............
what about having a userform pop up at the appropriate time in the
coding to have the user make his choices?  you could either already
have it set up & just load it at the right time, or build it
programmatically (waaaaaay beyond me).  then just hide it to keep the
values for the variables until you're ready to unload it.
just an idea.
susan






- Show quoted text -

what you have described is exactly what I am asking how to do. How do
I have the userform "pop up" at the right time and pull the input from
the userform? I have used these types of option boxes extensively
within worksheets, but never to gather input data from within a macro.

 

[Susan]

oh goodie, i got it right! ha ha.

build your small userform. then, in the appropriate place in your
coding, put

Load UserForm1
UserForm1.Show

you don't really need "load" unless you're pre-loading stuff - like
optionbutton1 should always be true when you initially show it. you'd
have to have a command button like "ok" & when the user presses that,
you'd have (in the click event)

Me.Hide

which would make the userform invisible but still loaded with the
options they've chosen. in that click event you can set the variables
you want from the options they've chosen, and then go on to continue
your macro using those variables.........

Set myButton.value = optionbutton1.value

in your main macro when you're done using the variables from the
userform, make sure you have

Unload Userform1 (or Unload Me if you're calling the command from
within the userform coding).

hope this gets you started!
susan

 

[Office_Novice]

After building the user form and add all the code needed. you will call the
userfrom in an event such as
workbook_open()
Userform1.show

or somthing like that. Have you done any programming before?

 

[A Mad Doberman]

Sounds like you need a user Form check this out.

http://www.contextures.com/xlUserForm02.html








- Show quoted text -

thanks, all!

 

[Office_Novice]

If you are off desining your userform good luck! If you get stuck reply to
this thred i will help as far as i can.