Solved

Get the value of a control on a worksheet

Posted on 2012-04-10
5
359 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now