Solved

Get the value of a control on a worksheet

Posted on 2012-04-10
5
362 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
  • 2
  • 2
5 Comments
 
LVL 33

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 250 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 33

Assisted Solution

by:Norie
Norie earned 250 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

838 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