Refer to sheet via class

[dugdugdug]

Can someone please tell me what is wrong with the following?

This is in module1:

Option Explicit

Sub test()

Dim abc As Class1

Set abc = New Class1

abc.Mysheet = "Sheet1"

End Sub

This is in Class1:

Option Explicit

Private pMySheet As Worksheet

Public Property Get Mysheet() As Variant

Mysheet = pMySheet

End Property

Public Property Set Mysheet(ByVal vNewValue As Variant)

pMySheet = vNewValue

End Property

When I run this, it says, "Object variable or With block variable not set"

Thanks

 

[Norie]

You're missing Set in the sub and the class.

'Module1
Sub test()

Dim abc As Class1

    Set abc = New Class1

    Set abc.Mysheet = Sheet1

End Sub

'Class1

Private pMySheet As Worksheet

Public Property Get Mysheet() As Variant

    Set Mysheet = pMySheet

End Property

Public Property Set Mysheet(ByVal vNewValue As Variant)

    Set pMySheet = vNewValue

End Property

 

[dugdugdug]

You're missing Set in the sub and the class.

'Module1
Sub test()

Dim abc As Class1

    Set abc = New Class1

    Set abc.Mysheet = Sheet1

End Sub

'Class1

Private pMySheet As Worksheet

Public Property Get Mysheet() As Variant

    Set Mysheet = pMySheet

End Property

Public Property Set Mysheet(ByVal vNewValue As Variant)

    Set pMySheet = vNewValue

End Property

Many thanks!