Selecting Optionbutton using VBA

**dwinmac** · 10-04-2008, 06:40 PM

I have two option buttons on each of five spreadsheets in my Excel workbook.

Selecting the first option button enables an x to be written to a cell when that cell is clicked with the mouse.

Selecting the second option button disables the writing of the x when the cell is clicked with the mouse.

I have these working quite well.

My problem is that I want the second option button selected (disable writing of x) when I enter the sheet. I'm pretty certain that I need the code in Sub Worksheet_Activate() but I haven't been able to find the VBA statement to set the second option button on.

Also, unlike having the option buttons on a form, when I select the option button on the spreadsheet in Design mode I can see no property sheet indicating the name, value, etc of the option button.

**mudraker** · 10-04-2008, 07:41 PM

I assume when you say Enable and disable the writting of an X in the cell you mean 1 option button places the X in the cell and the other option button removes the X

Unlike the Form Option buttons where you change a property value for these type of option buttons that are linked to a cell you change the cells value

If the 2 option buttons are linked to A1 then you would use VBA code

Range("a1").value = ""

or

Range("A1") = ""

**dwinmac** · 10-04-2008, 09:54 PM

The option buttons actually set and reset a public boolean variable that I check in the Worksheet_SelectionChange event to to see if the cell that I clicked on is "in range" and the "enable x to be written" optionbutton is selected.

I tried linking the option buttons to cells using the "Format Control" window and the Control tab -> Cell link:, as you suggested (I think). I set the first one to Y2 but when I opened the "Format Control" and the Control tab -> Cell link: for the second one it had Y2 in it as well.

What am I doing wrong here? I seems like it should be so simple. Do the two option buttons have to be grouped? I'm not sure I have that done properly yet.

I am using Office 2007 under XP runing onan iMac.

**dwinmac** · 10-04-2008, 11:06 PM

I just realized you said in your reply that both optionbuttons are linked to the same cell. Here's my code in the sheet activate subroutine:

Private Sub Worksheet_Activate()
     Range("y3").Value = "1"
     logReadOnly = True
End Sub

The two options buttons are linked to cell Y3:
optBut_03 - Sets the logReadOnly flag to FALSE
optBut_04 - Sets the logReadOnly flag to TRUE

The code Range("Y3")="1" causes optBut_03 to be selected instead of optBut_04.
The code Range("Y3")="" causes neither of the two optionbuttons to be selected.
By selected I mean the button has the filled in circle inside the outer circle.

**mudraker** · 10-05-2008, 01:09 AM

Please take a couple of minutes and read the Forum Rules - Rile 3

I have wrapped your code for you this time

I did make an error in my previous reply

You are correct blanking the cell unselects both option buttons

To select 1st option button use

Range("y3").value = 1

to select the 2nd option button use

Range("a1").value = 2

You are corect

By selected I mean the button has the filled in circle inside the outer circle.

**dwinmac** · 10-05-2008, 10:30 PM

Ok I think I finally got it. The right side of the statement contains the optionbutton number.

It turns out I have 17 option buttons on each of 5 sheets. The sheet I debugged first had the the two option buttons of interest named 1 and 2. All other 4 sheets had the two option buttons of interest named 17 and 18. I copied and pasted all buttons from the first sheet apparently and copied the two buttons of interest last. So, the code for the other 4 sheets had to be as follows:

Private Sub Worksheet_Activate()
    Range("y3").Value = "17"
    logReadOnly = True
End Sub

Only after discovering the "Selection Pane" under the "Home" tab and the "Find & Select" on the ribbon did it all start to make sense. Prior to that I had no idea what the names of the optionbuttons were.

Selecting the option buttons in Design mode (i.e. in order to link them to the cells) was a real pain until I got used to using the selection pane. Just setting the "Select Objects" under "Find & Select" on the ribbon and clicking on the option button caused a lot of frustration. I couldn't deselect them once they were selected by clicking on them.