CODEWITHSUNDEEP
vbaexcelcheckbox
JDCAce
JDCAce

Reputation: 159

Accessing Excel sheet-specific objects from a self-defined Worksheet object

I have a workbook containing one sheet with a checkbox. I am currently accessing that checkbox (named cbFee) using Sheets("Voltest").cbFee. This works as intended. However, I want to create a second workbook, so I've saved the first workbook containing my macros and the "Voltest" sheet as an object wbMacros. To help cut down on all the typing for wbMacros.Sheets("Voltest"), I've shortened it by creating a Worksheet object wsVoltest and set it to that worksheet.

However, when I then try to access the checkbox using wsVoltest.cbFee, I'm prompted with "Compile error: Method or data member not found."

Here's my code:

Module 1:

Public wbMacros As Workbook
Public wbFinish As Workbook
Public wsVoltest As Worksheet

Sub Voltest()
    Set wbMacros = ThisWorkbook
    Set wbFinish = Workbooks.Add
    Set wsVoltest = wbMacros.Sheets("Voltest")

    Application.run "TabNames"
End Sub

Module 2:

Sub TabNames()
Dim voltestSheet As Worksheet
Set voltestSheet = wbMacros.Sheets("Voltest")

' These two message boxes are for testing. They both display the correct value
MsgBox "wbMacros.Sheets: " & wbMacros.Sheets("Voltest").Name
MsgBox "Voltest sheet: " & voltestSheet.Name

' This If works just fine
If wbMacros.Sheets("Voltest").cbFee.Value Then
    MsgBox "If hit"
End If
' This If gives the compile error
If voltestSheet.cbFee.Value Then
    MsgBox "2nd If hit"
End If

I'm guessing wsVoltest.Name is working correctly because the Name property is inherent to all Worksheet objects, but checkboxes are not. How do I force the compiler to "know" about the objects that sheet contains?

Upvotes: 1

Views: 108

Answers (1)

Vityata
Vityata

Reputation: 43575

Considering the code below, your question can be rephrased to: Why Debug.Print ws.checkbox1.value gives an error and how to refer it:

Public Sub TestMe()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets(1)
    Debug.Print Worksheets(1).CheckBox1.Value

    'v----< Error if you uncomment!
    'debug.Print ws.CheckBox1.value

    'v----< This works:
    Debug.Print ws.OLEObjects("CheckBox1").Object.Value

End Sub

The answer is - with OLEObjects("name").Object

Upvotes: 1

Related Questions