Selecting Radio Buttons using a Macro

[jroo]

Hello,

I created two radio buttons on a spreadsheet where a user can select
one button for Yes and the other for No. When I right click these
buttons, I notice the names (shown on the left hand side of the
formula bar) are:

Option Button 3 (for No)
Option Button 4 (for Yes)

I'd like to create a macro that automatically selects the Yes or No
button depending on a cell value in another tab. If the cell value is
1, the macro should select Yes. If the value is 0 the macro should
select No.

I tried running the macro below:

Sub testSelectYesOrNo()
If Not IsEmpty(Sheets("Sheet2").Range("A1").Value) Then
Select Case Sheets("Sheet2").Range("A1").Value
Case Is = 1
Sheets("Sheet1").OptionButton4 = True
Case Is = 0
Sheets("Sheet1").OptionButton3 = True
End Select
End If
End Sub

But when this runs I get a "Run-time error 438 Object doesn't support this property or method"

When I click debug, the text "Sheets("Sheet1").OptionButton4 = True"
was highlighted.

Please help! Thanks...

 

[jroo]

Blade Hunter - by select, I mean I want the "Yes" radio button or the "No" radio button to be filled in black on the worksheet LSS

Ralejer - Were you asking Blade Hunter what option buttons he/she used?

I created the radio buttons using the forms toolbar. I'm not familiar with ActiveX controls...

 

[SMB Cooper]

If your Option Buttons is from the Forms Toolbar, then this code should work...

Sub testSelectYesOrNo()
If Sheets("Sheet2").Range("A1").Value <> "" Then
Select Case Sheets("Sheet2").Range("A1").Value
Case 1
With Sheets("Sheet1").OptionButtons("Option Button 4")
.Value = xlOn
End With
Case 0
With Sheets("Sheet1").OptionButtons("Option Button 3")
.Value = xlOn
End With
End Select
End If
End Sub

 

[Blade Hunter]

The ones I did for testing were at a sheet level using the Control Toolbox

 

[jroo]

SMB Cooper's solution worked perfectly. Thank you!

And thanks to everyone else too!

 

[Ralajer]

Jroo, I was asking you as your code worked for one type and not the other. Forms toolbar is the older version, Form Controls, and the Control Toolbox toolbar are the ActiveX Controls. Sorry for the terminology mixup. I use 2007 and they are labeled as I stated before and I forgot the were labeled differently in 2003.

Glad you found the solution.