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

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

**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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

to select the 2nd option button use

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.