CODEWITHSUNDEEP
excelvba
Eddy
Eddy

Reputation: 105

I want to know the difference between Sheet1 and setting worksheets

I have a piece of script that’s in module1 that checks if an option button is clicked.

The option button is placed in Sheet1, name “Info”, so I thought the script below would work

Sub checkClicked()
    dim Page as worksheet
    set Page as worksheets(“Info”)
    Debug.print Page.optClicked 
End sub

But when I did it like this it says method or data member not found. It would only work if I replace it with

… 
Debug.print Sheet1.optClicked
…

Can anyone give me an insight why this happens?

Upvotes: 0

Views: 1748

Answers (5)

gembird
gembird

Reputation: 14053

Other approach: the ActiveX controls on sheet are accessible from two collections: Shapes and OLEObjects. You could use the OLEObjects collection to get access to your checkbox.

Sub checkClicked()
    Dim Page As Worksheet
    Set Page = Worksheets("Info")

    ' 1/ ActiveX check box in Shapes collection
    Dim myShape As Shape
    Set myShape = Page.Shapes("optClicked")
    ' --------------------------------------

    ' 2/ ActiveX check box in OLEObjects collection
    Dim myOLEObject As OLEObject
    Set myOLEObject = Page.OLEObjects("optClicked")

    ' Use Object property to get access to your check box
    Dim myCheckBox As Variant
    Set myCheckBox = myOLEObject.Object

    If (TypeOf myCheckBox Is MSForms.CheckBox) Then
      Debug.Print myCheckBox.value
    End If
End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166156

Think of Sheet1 as a "subclass" of "worksheet" - when you add controls to the sheet you're adding new members. A generic worksheet object doesn't have a member which represents your option button, whereas Sheet1 does. 

Sub Test()

    Dim sht As Worksheet
    Dim sht1 As Sheet1

    Set sht = ThisWorkbook.Sheets("Sheet1")
    Set sht1 = Sheet1

    Debug.Print sht.optClicked  'error
    Debug.Print sht1.optClicked 'OK

End Sub

Upvotes: 2

Peter L.
Peter L.

Reputation: 7304

Try Set Page = Worksheets("Info") and do NOT use these curly “” quotes - just in case (for Excel formulas this DOES matter).

Upvotes: 1

Christian Sauer
Christian Sauer

Reputation: 10889

Set Page = ActiveWorkbook.Worksheets("Info") should work. I think worksheets is no real property in VBA...

Also, your debug print code looks weird, use debug.print("bla").. Do you have Option explicit activated?

Upvotes: 1

jeffrey_t_b
jeffrey_t_b

Reputation: 1769

The argument within Worksheets is the name of the worksheet you are interested in, i.e. "Sheet1".

Upvotes: 0

Related Questions