Hiding/Unhiding Sheet in a Workbook Using a Radio Button

[lakshya167]

Hi!

The Problem
So I'm making a form in Excel. I have two options buttons labelled "Yes" & "No". These two options buttons are in Sheet 1.

I want to make sheet 2 visible if any user selects "Yes" and hidden if a user selects "No".

I'm using Excel for Mac 2011 so there are no ActiveX controls.

What I've Done So Far
Insert two option buttons, named OptionButton1 (caption"Yes") and OptionButton2 (caption"No").

Clicked on Editor ->Double Clicked on Sheet1 (current sheet containing the 2 option buttons).
Typed in the following code:
Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
Sheets("Sheet2").Visible = xlSheetVisible
Else
Sheets("Sheet2").Visible = xlSheetHidden
End If
End Sub




Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then
Sheets("Sheet2").Visible = xlSheetHidden
Else
Sheets("Sheet2").Visible = xlSheetVisible
End If
End Sub

Clicked save.

Right clicked OptionButton1 -> Assign Macro -> OptionButton1_Click
Right clicked OptionButton2 -> Assign Macro -> OptionButton2_Click

The Error
When I click on the option button "Yes" or "No" the sheet 2 doesn't hide.
I get the error message: The macro 'Workbook1.xlsm!OptionButton1_Click' cannot be found.
Simmilar for Option Button 2.

Also, during the editor, when I click on the run button (play) I get the following error:
Runtime Error '424'
Object Required



Don't know what to do. I'm new to coding at excel, so please don't judge.

Thanks for the help! Much appreciated!

 

[shadow12345]

Try something like this....

Sub OptionButton1_Click()

Set shp1 = Worksheets("Sheet1").Shapes("Option Button 1")
      
If shp1.ControlFormat.Value = xlOn Then
Sheets("Sheet2").Visible = xlSheetHidden
End If
End Sub

 

[lakshya167]

Try something like this....

Sub OptionButton1_Click()

Set shp1 = Worksheets("Sheet1").Shapes("Option Button 1")
      
If shp1.ControlFormat.Value = xlOn Then
Sheets("Sheet2").Visible = xlSheetHidden
End If
End Sub

Now it giving me "Out Of Memory" error whenever I click on Option Button 1

 

[shadow12345]

Sub OptionButton1_Click()

Set shp1 = Worksheets("Sheet1").Shapes("Option Button 1")
      
If shp1.ControlFormat.Value = xlOn Then
Sheets("Sheet2").Visible = xlSheetHidden
Else
Sheets("Sheet2").Visible = xlSheetVisible

End If
End Sub

Maybe add the what to do if its not ticked? Like this, sorry im travelling now so I am on my phone.