Output from Frames - multiple optionbutton groups

[Monkeeman]

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

 

[BDeitrick]

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