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...

 

[Cymae]

I do not think you need to reference the sheet name. Try referencing the option button directly..it may work better

 

[jroo]

I tried getting rid of the sheet name, but that didn't seem to work.

Just to see if the radio buttons would be selected, I tried running this code:

sub MyClickYes
OptionButton4=true
end

But this did nothing either. Not even an error message came up.

????

 

[Cymae]

form.optionbutton4=true?

 

[Blade Hunter]

Is the sheet name sheet1? if you are refering to the sheet reference rather than the name, take out the double quotes and remove the word sheet (So it becomes Sheets(1) instead).

 

[jroo]

I used "Sheet1" just as an example. The "Yes" and "No" radio buttons are actually on a worksheet named "LSS". I created the two radio buttons using the Forms Toolbar, which automatically gave the buttons the names "Option Button 3 and "Option Button 4"

I'm not sure if I would need to change the reference names to these buttons in vba to include "underscores" (i.e. Option_Button_3)

Got any other ideas? Thanks again

 

[Blade Hunter]

The names should be exactly as they are in the properties window.

I usually change them to something meaningful to my code for easier use.

 

[jroo]

I changed the "OptionButton4" to "LssYes" and "OptionButton3" to "LssNo"... But I still can't figure out what code to use just to simply select one of these radio buttons. Is this even possible (using the radio buttons from the forms toolbar)?

 

[Blade Hunter]

By select, you mean you want to change the state between true and false or you just want to read the state?

If you want to read it it would be Sheets("LSS").LssYes which would return a boolean.

I just set up a sheet and couple of buttons and named them as you have yours and ran your code (couple of minor changes like sheet name to LSS, also you don't need is = in a select case statement). Everything worked fine for me:

Sub testSelectYesOrNo()
If Not IsEmpty(Sheets("Sheet2").Range("A1").Value) Then
    Select Case Sheets("Sheet2").Range("A1").Value
    Case 1
        Sheets("LSS").LssYes = True
    Case 0
        Sheets("LSS").LssNo = True
    End Select
End If
End Sub

 

[Ralajer]

What type of option buttons are you using? Form Controls or ActiveX Controls? Your solution works for ActiveX controls but not for the form controls. I am not sure why that is but I tried it with both and the form controls didn't work with that particular code.