If Optionbutton value is true then

[Zwicky]

Hi There,

Hopefully an easy one for someone...

What code do I use in Excel vba to do an if/then statement depending on whether an option button is checked or not?

My optionbutton is called "ob1" and it's in sheet "Vary".

I tried
If Sheets("Vary").Shapes("ob1").Value = True Then

But it gives the error "object doesn't support this property or method"

Thanks!

 

[GlennUK]

Try:

If ob1.Value = True Then

or even:

If ob1.Value Then

... if you've really named your option button "ob1" that is.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[rjoubert]

Or you could use the .Checked property instead of the .Value property...

If ob1.Checked Then
...

 

[Zwicky]

Thanks Guys,
I tried these but I get RunTime Error 424: Object Required. Any ideas?

 

[KenPuls]

Hi Zwicky,

Did you create this button from the Control Toolbox or the Forms toolbar? They are different items, so need to be treated different ways...

The forms button is treated like this:

Sub FormsOptionButtonTest()
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes("Option Button 1")

    Select Case shp.ControlFormat.Value
        Case Is = -4146
            MsgBox shp.Name & " is not selected"
        Case Is = 1
            MsgBox shp.Name & " is selected"
    End Select

End Sub

But a button from the control toolbox is an ActiveX control, and needs to be treated differently. This is just one way of doing that:

Sub ActiveXOptionButtonTest()
    If Sheets("Vary").ob1.Value = True Then
        MsgBox Sheets("vary").ob1.Name & " is selected"
    Else
        MsgBox Sheets("vary").ob1.Name & " is not selected"
    End If
End Sub

If you can't remember which way you created it, try and right click it. If you can't, chances are pretty good it's an ActiveX button (you need to activate Design mode first). ActiveX buttons, when you do right click in design mode, allow you to adjust properties and "View Code", while Forms buttons do not. You can "Assign Macro" to a forms button, but it is not an on_click event specific to the button as is the ActiveX control.

HTH,

Ken Puls, CMA

http://www.excelguru.ca

 

[fumei]

If it is an ActiveX option button named ob1 then

ob1.Value = True

should work.

If it is a Forms option button then

Sheets("Vary").Shapes("Option Button 1").OLEFormat.Object.Value = -4146

means it is unchecked. 1 = checked.

Gerry
My paintings and sculpture

 

[xlbo]

For future reference, please post VBA questions in the VBA forum - Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Ah...forgot that.

Gerry
My paintings and sculpture

 

[Zwicky]

Thanks everyone,

I actually found another way of doing it which is to assign a cell link and use the value of that range as the reference instead. That worked fine.

Sorry, I didn't realise there was a vba forum...thanks for the tip!