Loop through option buttons in VBA

[Lenka]

Hello, I need your help with form controls in VBA.
I want to write a code that would reset to false the option buttons dependent on previous one. I need something like this: If the option button 1 is selected then turn off and make invisible option button 25-58.
Problem is I have a lot of option buttons in the sheet and the numerical order in name is not allways continuous as I needed to delete some option buttons when making the form.
Is there a way to detect them so I will not have to write millions of codes for every option button separately?
Thank you for your advise

 

[6StringJazzer]

Are your option buttons in a UserForm?
If they are in a worksheet, are they Forms controls or ActiveX controls?

The code will be different in each of these situations, but here is one example where they are in a UserForm:

Private Sub OptionButton1_Click()

   Dim i As Long
   Dim C As Control
   
   If OptionButton1 Then
   
      On Error Resume Next
      For i = 25 To 58
      
         Set C = Controls("OptionButton" & i)
         
         If Err.Number = 0 Then
            C.Value = False
            C.Visible = False
         Else
            Err.Clear
         End If
      
      Next i
      
   End If

End Sub

 

[Lenka]

Are your option buttons in a UserForm?
If they are in a worksheet, are they Forms controls or ActiveX controls?

The code will be different in each of these situations, but here is one example where they are in a UserForm:

Private Sub OptionButton1_Click()

   Dim i As Long
   Dim C As Control
  
   If OptionButton1 Then
  
      On Error Resume Next
      For i = 25 To 58
     
         Set C = Controls("OptionButton" & i)
        
         If Err.Number = 0 Then
            C.Value = False
            C.Visible = False
         Else
            Err.Clear
         End If
     
      Next i
     
   End If

End Sub

I am not using UserFom. They are in a worksheet and they are form controls.

 

[6StringJazzer]

Assign this macro to Option Button 1:

Public Sub OptionButton1_Click()

   Dim i As Long
   Dim C As Control
   
   Debug.Print ActiveSheet.Shapes("Option Button 1").ControlFormat.Value
   
   
   If ActiveSheet.Shapes("Option Button 1").ControlFormat.Value = xlOn Then

      On Error Resume Next
      For i = 2 To 6 ' 25 To 58
         ActiveSheet.Shapes("Option Button " & i).ControlFormat.Value = xlOff
         If Err.Number = 0 Then
            ActiveSheet.Shapes("Option Button " & i).Visible = False
         Else
            Err.Clear
         End If

      Next i

   End If
   
End Sub

 

[Lenka]

Assign this macro to Option Button 1:

Public Sub OptionButton1_Click()

   Dim i As Long
   Dim C As Control
  
   Debug.Print ActiveSheet.Shapes("Option Button 1").ControlFormat.Value
  
  
   If ActiveSheet.Shapes("Option Button 1").ControlFormat.Value = xlOn Then

      On Error Resume Next
      For i = 2 To 6 ' 25 To 58
         ActiveSheet.Shapes("Option Button " & i).ControlFormat.Value = xlOff
         If Err.Number = 0 Then
            ActiveSheet.Shapes("Option Button " & i).Visible = False
         Else
            Err.Clear
         End If

      Next i

   End If
  
End Sub

Thank you very much, that worked The error codes were what I was missing in my code.