Solved

Option buttons value = False

Posted on 2014-09-19
9
512 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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40333829
Yup. :-)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now