Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Option buttons value = False

Posted on 2014-09-19
9
Medium Priority
?
724 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 49

Accepted Solution

by:
Martin Liss earned 1800 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 200 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 49

Expert Comment

by:Martin Liss
ID: 40333822
Glen, wouldn't that be a problem if there were other shapes on the sheet?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 49

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

688 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