Solved

Option buttons value = False

Posted on 2014-09-19
9
554 Views
Last Modified: 2014-09-19
Folks,
I've tried this which is not working:
Private Sub Worksheet_Activate()
Dim optButton As OptionButton
    
    For Each optButton In OptionButons.OptionButton
        optButton.Value = False
    Next optButton
End Sub

Open in new window

The objective is that when the worksheet is activated my option buttons are all set to FALSE.
The option button are Grouped as Group 6
They are also labeled:
optButton1
optButton2
optButton3
The name of the worksheet is "OptionButtons"
0
Comment
Question by:Frank Freese
  • 3
  • 3
  • 3
9 Comments
 
LVL 46

Accepted Solution

by:
Martin Liss earned 450 total points
ID: 40333809
    With ActiveSheet
        .Shapes("Option Button 1").ControlFormat.Value = False
        .Shapes("Option Button 2").ControlFormat.Value = False
        .Shapes("Option Button 3").ControlFormat.Value = False
    End With

Open in new window

0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 50 total points
ID: 40333815
Alternatively (does not reference shape names):
Private Sub Worksheet_Activate()
    Dim optButton As Shape
    For Each optButton In Me.Shapes
        Shapes(optButton.Name).ControlFormat.Value = xlOff
    Next optButton
End Sub

Open in new window


-Glenn
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40333822
Glen, wouldn't that be a problem if there were other shapes on the sheet?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40333829
Yup. :-)
0
 

Author Closing Comment

by:Frank Freese
ID: 40333852
Martin I was leaning towards your solution until I read a article and tried what I proposed - great job.
Glenn, I'm going to keep your solution in case I ever need to clear all shape objects.
Thanks gentlemen!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40333860
Unfortunately Glenn's solution will fail on any sheet that has a Shape (like a non-ActiveX command button) that doesn't have a 'Value' property.
0
 

Author Comment

by:Frank Freese
ID: 40333870
Thanks Martin
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40333874
Martin is correct; my subroutine does not work as shown.  If the option button default names are not changed, then this will work instead:
Private Sub Worksheet_Activate()
    Dim optButton As Shape
    For Each optButton In Me.Shapes
        If Left(optButton.name,6) = "Option" then
            Shapes(optButton.Name).ControlFormat.Value = xlOff
        End If
    Next optButton
End Sub

Open in new window


Regards,
-Glenn
0
 

Author Comment

by:Frank Freese
ID: 40333899
Thanks Glenn for the follow-up
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question