Get the value of a control on a worksheet

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
Mark DalleyInformation AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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
Mark DalleyInformation AnalystAuthor Commented:
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
kgerbChief EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
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
Mark DalleyInformation AnalystAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.