0

I want to make an Excel radio button active/inactive, depending on the value of another cell.

I made the following attempt:

Sub test()
If ActiveSheet.Cells(9, 3).Text = 3 Then
    ActiveSheet.OptionButton20_Click.Active = False
Else
    ActiveSheet.OptionButton20_Click.Active = True
End If
End Sub

In the VBA macro window, but I got the following error message:

"Object doesn't support this property or method"

How can I fix it please? (Excel Mac 2011)


To clarify, when I say "inactive", I mean greyed out - I don't just mean that it has not been selected.

For example:

Group 1 of Radio Buttons: Male o Female o

Group 2 of Radio Buttons: Glasses o Earrings o Beard o

In this case, if "Female" is selected, I want "Beard" to become unselectable.


I now have the following text in my "Module 1":

Sub hide_group_box()
ActiveSheet.GroupBoxes.Visible = False
End Sub

Sub disable_radio()
If ActiveSheet.Cells(3, 3).Text = 3 Then
    ActiveSheet.OptionButton65.Enabled = False
End If
End Sub

The first thing, which hides the Group Boxes works fine. The second thing doesn't seem to work. Do I still have it wrong?

5
  • 1
    Possible duplicate of How to select radio buttons by cell value in Excel?
    – CharlieRB
    Jan 13 '16 at 21:21
  • Thanks. It's a different question, but I have added a clarification. Jan 13 '16 at 22:26
  • 1
    The property for which you're looking is called Enabled
    – Kyle
    Jan 14 '16 at 2:21
  • Thanks. I still get the error message detailed in my post though. It highlights the line 'ActiveSheet.OptionButton20_Click.Enabled = True' Jan 14 '16 at 9:44
  • 1
    Remove the _Click? Jan 14 '16 at 13:12
0

just so happens I was working on this the other day and had the same problem.

I have three options, Likes, Dislikes, None. Only one can be selected because they are radio buttons.

What you want to do is set your variables as such:


Sub doSelectedTest()

    Dim shapeLikes As Shape
    Dim shapeDislikes As Shape
    Dim shapeNone As Shape


    Set shapeLikes = Worksheets("picks").Shapes("Option Button 6")
    Set shapeDislikes = Worksheets("picks").Shapes("Option Button 7")
    Set shapeNone = Worksheets("picks").Shapes("Option Button 8")

    If shapeLikes.ControlFormat.Value = xlOn Then
        MsgBox "Likes"
    End If
    If shapeDislikes.ControlFormat.Value = xlOn Then
        MsgBox "Dislikes"
    End If
    If shapeNone.ControlFormat.Value = xlOn Then
        MsgBox "NONE"
    End If
End Sub

So in your case:

Dim myShape As Shape
Set myShape = Worksheets("picks").Shapes("Option Button 65")

If ActiveSheet.Cells(3, 3).Text = 3 Then
    myShape.ControlFormat.Value = xlOff
End If

Or if Button 65 is desired to be disabled in this scenario, then myShape.ControlFormat.Enabled = False or .Visible = False

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.