Forms and Controls

[ABC]

I am coding using VBA for Excel 2000.

I have a form, on this form I want to have a label (or text box) and 3
radio buttons, each radio button will have different values, 0,3 and 5
for example.

What these are are questions with one possible answer, Low, Medium or
High (low,medium and High will not always be worth 0,3 and 5).

Somehow I would like to have this form read a spreadsheet for the
questions and values for the radio buttons. I think if I knew how to
create control arrays dynamically this would work. Can anyone tell me
how to do that or give me another idea on how to do this.

Thanks

ABC

 

[Dave Peterson]

I had a worksheet with 5 columns and a form with 2 labels and 3 option buttons.

In the worksheet, columns were:
A: Question #
B: Question text
c:e option button values

Somewhere in code, I could do this:

Sub loadValues(myCell As Range)

Me.Label1.Caption = "Question: " & myCell.Value
Me.Label2.Caption = myCell.Offset(0, 1).Value

Me.OptionButton1.Tag = myCell.Offset(0, 2).Value
Me.OptionButton2.Tag = myCell.Offset(0, 3).Value
Me.OptionButton3.Tag = myCell.Offset(0, 4).Value

Me.OptionButton1.Value = True

End Sub

By passing a range to the sub, like:

Call loadValues(curCell)

Later on if I wanted to keep track of the points, I could do this:

MsgBox (-Me.OptionButton1.Value * Me.OptionButton1.Tag) _
+ (-Me.OptionButton2.Value * Me.OptionButton2.Tag) _
+ (-Me.OptionButton3.Value * Me.OptionButton3.Tag)

'write it to another sheet????

I don't speak the VB, but VBA doesn't support control arrays.