+ Reply to Thread
Results 1 to 6 of 6

Selecting Optionbutton using VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    10-04-2008
    Location
    New York
    Posts
    7

    Selecting Optionbutton using VBA

    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.
    Last edited by dwinmac; 10-05-2008 at 10:32 PM.

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    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") = ""
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Registered User
    Join Date
    10-04-2008
    Location
    New York
    Posts
    7
    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.

  4. #4
    Registered User
    Join Date
    10-04-2008
    Location
    New York
    Posts
    7
    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.
    Last edited by mudraker; 10-05-2008 at 01:04 AM.

  5. #5
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    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.

  6. #6
    Registered User
    Join Date
    10-04-2008
    Location
    New York
    Posts
    7
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Locating and selecting a column with VB Script
    By bigtonyicu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2008, 02:17 PM
  2. Selecting All Buttons
    By jonn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-28-2007, 09:17 PM
  3. OptionButton Properties Descriptions ?
    By dpenny in forum Excel General
    Replies: 2
    Last Post: 04-16-2007, 08:18 PM
  4. OptionButton weird behavior
    By dpenny in forum Excel General
    Replies: 2
    Last Post: 04-16-2007, 08:06 PM
  5. Selecting a graph using macro
    By Dagwood2005 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-20-2007, 05:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1