Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA: clicking a radio button by VBA

    I have a set of option buttons on the worksheet (not a form), linked to a cell. My code often changes the contents of the cell directly, but the option buttons don't change to reflect it.

    All the suggestions I've seen involve code in the WorksheetChange event, but there's an awful lot going on there already. (For one thing, there are times when EnableEvents is disabled.) Besides which, I have two different worksheets with the same issue, so I'd prefer to use a macro in a module rather than in two different Events.

    I know that in a macro, the option button is called something like CurSheet.OLEObjects("OptionButton1"), but I can't figure out how to click it.

    (If I do have to use an Event, what can I use other than Worksheet Change?)

    The buttons are OLEObjects (that means ActiveX, not form controls, right?).

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,939
    Thanks
    0
    Thanked 94 Times in 90 Posts
    If it is ActiveX:
    Code:
    Currsheet.oleobjects("OptionButton1").object.value = true
    should do the trick.
    Regards,
    Rory
    Microsoft MVP - Excel.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,476
    Thanks
    211
    Thanked 848 Times in 780 Posts
    Building on Rory's code how about:
    Code:
    Currsheet.oleobjects("OptionButton1").object.value = [a1].value
    This code assumes that you are putting True/False into the cell and that A1 is the specified cell. You could also use an immediate if it you have other values, e.g.
    =iif([a1].value=x,True,False)
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #4
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect! I had no idea about the ".object" property. Thanks!

Tags for this Thread

Posting Permissions

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