?
Solved

Get the value of a control on a worksheet

Posted on 2012-04-10
5
Medium Priority
?
370 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 35

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 35

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

864 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