Solved

Option buttons value = False

Posted on 2014-09-19
9
606 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 47

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 47

Expert Comment

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

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 47

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

751 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