Toggle optionbutton (from forms)

[Johnlemons]

I need to know how to toggle a single optionbutton (from forms).
If its on and I click it, it needs to go off.

Also I need to be able to see if its on/off from another sub.
ie if optionbutton1=true then .......

I've tried the record macro button but can't get it to work.
Thanks for your help,
John.

[Tommy]

Hi John,
I don't undestand the on for optionbuttons, is it the value or enabled or visible?

Private Sub OptionButton1_Click()
    If OptionButton1.Value Then
        OptionButton1.Value = False
    End If
    If OptionButton1.Visible Then
        OptionButton1.Visible = False
    End If
    If OptionButton1.Enabled Then
        OptionButton1.Enabled = False
    End If
End Sub

Sub AnotherSubToCheckOption()
    If UserForm1.OptionButton1.Value Then
        'code for optionbutton
    End If
    If UserForm1.OptionButton1.Visible Then
        'code for optionbutton
    End If
    If UserForm1.OptionButton1.Enabled Then
        'code for optionbutton
    End If
End Sub

[Johnlemons]

I was talking about .value method. I used some of your code which seemed to me like it should work but didn't. I used both methods (forms and control box) nothing worked. Put it in the sheet code and the module and it didnt work.
I uploaded it to http://www.geocities.com/john_lemons/test1.xls so you could take a look and see if it works on yours. Maybe its my computer. (office xp verision).

[Tommy]

On the optionbutton1 I added the Sheet1 and it works for me on WinXP pro with O2k

Private Sub OptionButton1_Click()
    a = 2
    If Sheet1.OptionButton1.Value Then
        a = Sheet1.OptionButton1.Value
        Sheet1.OptionButton1.Value = False
    Else
        Sheet1.OptionButton1.vaule = True
    End If
End Sub

I am still working on the one with the optionbutton3 that has an assigned macro

[Zack Barresse]

Cross-posted: http://www.mrexcel.com/board2/viewtopic.php?t=157767

[Johnlemons]

Tommy thanks for helping me out. I got the first one to atleast go through the code (only goes through when checking the button from off to on. Will not it its already been checked. This one that on was added from the control box. Thats not gonna work. It needs to be added from the forms box. ie. The one your working on now, optionbutton2 (caption says three, forgot to change it.) That one is assigned a macro. its the one that needs to work.

Thanks,
John...

[Tommy]

What are you trying to do? I can't seem to get a handle on it. I may could come up with an alternate way

[Johnlemons]

I'm trying to have an optionbutton on the spreadsheet.
If I click on the optionbutton, it will toggle. (if its on, it will turn off. vice versa)
right click in the toolbar where nothing is at. A menu will appear. click on forms toolbar. Use this one to add an optionbutton.

The optionbutton you said you were working on (the one assigned to the macro) it needs to just toggle. thats it. Not a simple as it seams though.

John.

[Tommy]

OK
Right click the control - Note this MUST be a form control - in the dialog box pick the tab at the top that says Control, where it says cell link type in a cell address as in C53 (this is the one I used for testing). The code is as follows:

Sub OptionButton2_Click()
    If Worksheets("Sheet1").Range("C53").Value = 1 Then
        Worksheets("Sheet1").Range("C53").Value = 0
    Else
        Worksheets("Sheet1").Range("C53").Value = 1
    End If
End Sub

The above code will never allow a value of 1. Why? because the option button is setup for changing the value to 1 when clicked, then it will run the macro which will set the value to 0.

Happy Coding

[Bob Phillips]

I think your problem stems from trying to set singletomn option buttons on or off. They just are not designed that way, they are designed to be part of a group, with one in the group being on, all others being off.

If you want a toggling type of control, use a checkbox.

[Johnlemons]

Thanks Tommy,
That worked fine.

[Tommy]

Glad I could help

Let me know if there you need more help.