many optionbuttons in a form need help to make classes

Hi I've made a form with 7 steps to be filled out. Each step has a group containing 9 optionbuttons that are colored from green to red shades. The choice made for each group, corresponds with the numbers 1 to 9 that have to be shown on one cell on a worksheet.
I have made it all working, but newby as I am, I've written the code probably with far too many subs and functions - now I get an Runtime error "28" Out of stackspace...
I know the optionbuttons have to be made easier to handle with classes, but I do not know how to handle this.
Could someone please help me on my way?

Thanks Trudie

Here's a part of my coding for one optionbuttongroup:

'----------------------------
' All values for optionboxes
'----------------------------
'optionboxes Page2
'----------------
Private Sub MultiPage1_Change()
opt1Step1.BackColor = RGB(165, 0, 0)
opt2Step1.BackColor = RGB(205, 0, 0)
opt3Step1.BackColor = RGB(240, 100, 25)
opt4Step1.BackColor = RGB(255, 185, 30)
opt5Step1.BackColor = RGB(180, 230, 230)
opt6Step1.BackColor = RGB(120, 205, 200)
opt7Step1.BackColor = RGB(140, 200, 65)
opt8Step1.BackColor = RGB(90, 135, 40)
opt9Step1.BackColor = RGB(45, 115, 30)

opt9Step1.Caption = "9. Exceeds expectations"
opt8Step1.Caption = "8. Excellent performance"
opt7Step1.Caption = "7. Strong performance"
opt6Step1.Caption = "6. Above average performance"
opt5Step1.Caption = "5. Average performance"
opt4Step1.Caption = "4. Below average performance"
opt3Step1.Caption = "3. Poor performance"
opt2Step1.Caption = "2. Extremely poor performance"
opt1Step1.Caption = "1. Unacceptable performance"

opt1Step1.Width = 165
opt2Step1.Width = 165
opt3Step1.Width = 165
opt4Step1.Width = 165
opt5Step1.Width = 165
opt6Step1.Width = 165
opt7Step1.Width = 165
opt8Step1.Width = 165
opt9Step1.Width = 165
End Sub



Private Sub cmdNextPage2_Click()

Dim ValueSelected As Boolean
Dim i As Long

ValueSelected = False
If Me.opt1Step1.Value = True Then ValueSelected = True
If Me.opt2Step1.Value = True Then ValueSelected = True
If Me.opt3Step1.Value = True Then ValueSelected = True
If Me.opt4Step1.Value = True Then ValueSelected = True
If Me.opt5Step1.Value = True Then ValueSelected = True
If Me.opt6Step1.Value = True Then ValueSelected = True
If Me.opt7Step1.Value = True Then ValueSelected = True
If Me.opt8Step1.Value = True Then ValueSelected = True
If Me.opt9Step1.Value = True Then ValueSelected = True

ElseIf ValueSelected = False Then
MsgBox "Please Rate the HSE & Ethics.", vbExclamation, "Rating HSE & Ethics"
End If
End Sub

'writing values from optionbuttongroup1 to Sheet4 SupplierRating

Private Sub opt1Step1_click()
If opt1Step1.Value = True Then Sheet4.Range("B5").Value = 1
End Sub
Private Sub opt2Step1_click()
If opt2Step1.Value = True Then Sheet4.Range("B5").Value = 2
End Sub
Private Sub opt3Step1_click()
If opt3Step1.Value = True Then Sheet4.Range("B5").Value = 3
End Sub
Private Sub opt4Step1_click()
If opt4Step1.Value = True Then Sheet4.Range("B5").Value = 4
End Sub
Private Sub opt5Step1_click()
If opt5Step1.Value = True Then Sheet4.Range("B5").Value = 5
End Sub
Private Sub opt6Step1_click()
If opt6Step1.Value = True Then Sheet4.Range("B5").Value = 6
End Sub
Private Sub opt7Step1_click()
If opt7Step1.Value = True Then Sheet4.Range("B5").Value = 7
End Sub
Private Sub opt8Step1_click()
If opt8Step1.Value = True Then Sheet4.Range("B5").Value = 8
End Sub
Private Sub opt9Step1_click()
If opt9Step1.Value = True Then Sheet4.Range("B5").Value = 9
End Sub

Hi Kyle, thanks for your fast reply - I thought of classes becaouse i thought i then would need less space for describing the optionboxes width, hight, colour and name. (I have 7 groups of similar buttons, with a rating from 1 to 9).

When I look at your coding, could you please explain me what the "Left, Split and Right" refere to?
regards,
Trudie

Originally Posted by snb

I think 1 group of 9 optionbuttons will suffice.
You can use that group for as many occasions as necessary.
You can even show that group in different places very simply if you put them in a frame.

alternative code:

sub snb()
  for each ct in controls
    if lcase(typename(ct))="optionbutton" then x=x+iif(ct,val(ct.caption),0)
  next
  sheets(1).cells(1,5)=x
end sub

Hi snb, could you please explain the codebit "then x=x+iif(ct,val(ct.caption),0)"
what does it ?
Newby as I am - I want to learn by doing - and not just copying bits and peaces.
regards, Trudie

Hi Kyle, I've tried working with your code, and put it into the 'next page button'
so it will fill out the value to the worksheet when going to the next page.
(i have use a form on a multipage with 8 pages).
It works fine on the first page, but when I insert the code to the next page, it will not work.
I have to say that I donnot understand fully what your code is doing - could you explain it to me, so I can use it for all the seven groups (with each 9 optionbuttons)?
Regards,
Trudie

Hi Kyle, thanks again, - I will post the total file later this week, I'll have to change some descriptions, since parts of the file are classified as confidential
Really hope you want to take a look at it -
regards,
Trudie

I would have added comments to my code, but snb's is easier to understand

If you would still like me to explain let me know

Thanks snb, sorry about the quotation... I'll trie it out and hope I can get back to the forum in case I won't succeed.
regards,Trudie

Hi impartrudie,

It's difficult to say without seeing your workbook, basically the code takes the name of the option button and uses this to set the cell i.e opt3step1 would pull out the 3 and use this as the grade, it does however assume that you are using this naming convention throughout your form.

snb's code is a little more resilient if you are changing your naming convention as it simply loops through all the option buttons and pulls the first number from the caption.

You should really look at using only one set of option buttons, this will make your code smaller and more manageable.