mercredi 21 mars 2018

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?




Aucun commentaire:

Enregistrer un commentaire