?
Solved

Get the value of a control on a worksheet

Posted on 2012-04-10
5
Medium Priority
?
367 Views
Last Modified: 2012-04-10
Dear Experts

I have a pair of option buttons embedded on a sheet. They alternate true and false values between each other just as they should.

But, How do I retrieve the value of an option button inside Excel VBA, when the option button is NOT on a form?

NB Neither of them are linked to a cell. The values given to them do however persist through a save/reload, so must be stored somewhere.

I have searched the help, and EE, yet not found the answer to what seems a very simple question. All the examples I have seen assume that a form is being used.

Many thanks in advance.

Hopeful Kiwi
0
Comment
Question by:kiwi_731
[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
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 37828292
You don't say which type of controls they are, ActiveX or Forms.

If we assume they are ActiveX then put this code in the worksheet module of the worksheet they are on.
Private Sub OptionButton1_Click()
    Select Case True
        Case OptionButton1.Value
            Range("A1") = "OptionButton1"
        Case OptionButton2.Value
            Range("A1") = "OptionButton2"
    End Select
End Sub

Private Sub OptionButton2_Click()
    Select Case True
        Case OptionButton1.Value
            Range("A1") = "OptionButton1"
        Case OptionButton2.Value
            Range("A1") = "OptionButton2"
    End Select
End Sub

Open in new window

0
 

Author Comment

by:kiwi_731
ID: 37828352
They are actually Forms controls.

Also, I need to be able to retrieve the value of the control as soon as the worksheet opens (before anything gets clicked and loses the previous state), so putting it into a click event handler is too late, so to speak.

Hopeful Kiwi
0
 
LVL 12

Accepted Solution

by:
kgerb earned 1000 total points
ID: 37828435
For a Forms control try this.  Put this in the code pane for the worksheet containing the option buttons.

Private Sub Worksheet_Activate()
Debug.Print ActiveSheet.DrawingObjects("Option Button 1").Value
End Sub

Kyle
0
 
LVL 34

Assisted Solution

by:Norie
Norie earned 1000 total points
ID: 37828485
You didn't say when you wanted to find out the values of the option buttons (as wel as what type they were.)

Not sure how you want to see the values but perhaps this.
Private Sub Worksheet_Activate()
    Select Case 1
        Case Me.OptionButtons(1).Value
        
        MsgBox Me.OptionButtons(1).Caption & " is True"
    Case Me.OptionButtons(2).Value
        
        MsgBox Me.OptionButtons(2).Caption & " is True"
    
    End Select
End Sub

Open in new window

0
 

Author Comment

by:kiwi_731
ID: 37828970
Hi Kyle / imnorie

Both your latest methods work fine.

Who would have guessed that an option button is a drawingObject! The help is basically silent about DrawingObjects, likewise about the fact that a worksheet has an OptionButtons collection, and OptionButton properties.

The help did however disclose that the two possible values of an option button revealed by kgerb's code (1 and -4146) correspond to the constants xlOn and xlOff respectively. Not what one would call an intuitive correspondence.

Many thanks to both of you - I will split the points equally.

Hopeful Kiwi

P.S. EE made me choose a best solution but I think they were both equally good / instructive.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 the scrolling table in Microsoft Excel using the INDEX function.

766 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