MrExcel Publishing
Your One Stop for Excel Tips & Solutions

option button loop


Posted by steve on March 26, 2001 8:58 PM

Is there a way to loop through like 100 option buttons and change the reference cell or group name to cell A1 for option buttton1 and continue through A100 option button 100. I know theres a way but, can't seem to get it though.
thanks steve


Posted by David Hawley on March 27, 2001 3:12 AM


Hi Steve

This code will do it for all option buttons at run time:

Private Sub CommandButton1_Click()
Dim MyControl As Control
Dim MyName As String
Dim MyNumber As Integer
For Each MyControl In Me.Controls
If MyControl.Name Like ("Option*") Then
MyName = MyControl.Name
Select Case Len(MyName)
Case 13 '1 to 9
MyNumber = CInt(Right(MyControl.Name, 1))
MyControl.GroupName = Cells(MyNumber, 1)
Case 14 '10 To 99
MyNumber = CInt(Right(MyControl.Name, 2))
MyControl.GroupName = Cells(MyNumber, 1)
Case 15 '100 To 999
MyNumber = CInt(Right(MyControl.Name, 3))
MyControl.GroupName = Cells(MyNumber, 1)
End Select
End If
Next MyControl

End Sub

Dave


OzGrid Business Applications