Output from Frames - multiple optionbutton groups

M

Monkeeman

Joined
Aug 2, 2011
Messages
3
Reaction score
0
Hi

I'm creating a Userform in Excel that will be a checklist. Basically, there will be a large number of questions (20), each of which will have three possible answers "Yes-No-N/A". The plan is to use option buttons grouped into frames. So my form will look something like:


Frame1
Did the user perform step 1? O Yes O No O N/A

Frame2
Did the user perform step 2? O Yes O No O N/A


I know how to read output from the option buttons themselves (IF optionbutton.value = true THEN etc etc), but since I am going to have 60 option buttons, I was hoping there was a way to read an output from the 20 frames instead.

Ideally, for each frame, I would like to have it output a value depending on which option button is clicked. So if
Yes =1
No =2
N/A =3
and in Frame1 the user selected "yes", then Frame1 should return a value of 1. Frame2 would have an output of 3 if "N/A" is checked, and so on.

So my question is: Can a frame hold a value? Can someone show me an example of code that will do what I describe above?

Also, can option buttons in different frames have the same name? Can I just have OptYES, OptNO and OptNA appear 20 times in the same form if they are in different frames?

Thanks!
Mike
 
VBA does not support arrays of controls, so they each need unique names. But you can store the selection using the tag property of the frame:

For the option buttons on Frame1:
Private Sub OptionButton1_Click()
UserForm1.Frame1.Tag = "Yes"
End Sub
Private Sub OptionButton2_Click()
UserForm1.Frame1.Tag = "No"
End Sub
Private Sub OptionButton3_Click()
UserForm1.Frame1.Tag = "NA"
End Sub

And for the option buttons on Frame2:
Private Sub OptionButton4_Click()
UserForm1.Frame2.Tag = "Yes"
End Sub
Private Sub OptionButton5_Click()
UserForm1.Frame2.Tag = "No"
End Sub
Private Sub OptionButton6_Click()
UserForm1.Frame2.Tag = "NA"
End Sub

Then read the Frame tags to get the values.

Bernie
 
Back
Top