Referencing the worksheet that has previously been assigned to a userform

[RawlinsCross]

If I assign a userform a specific worksheet, how then do I reference that worksheet without specifically referencing the name of the worksheet?

Public Sub CCD_REPORT_Click()

Worksheets("Report365").EnableCalculation = True
Worksheets("Report365").Calculate

 '--creates instance of roadmap userform, passes variables to it,
 '     then shows modeless
 
 '--declare variable frm to be a A222ALRTCCDREPORT userform object
 Dim frm As A222ALRTCCDREPORT
 
 '--create a new instance of A222ALRTCCDREPORT
 Set frm = New A222ALRTCCDREPORT
 
 '--pass worksheet with headers and data to public property of frm
 Set frm.Worksheet = ThisWorkbook.Sheets("Report365")
 
 '--show frm to allow user to interact
 frm.Show vbModeless
 
End Sub

Can't I now reference the data through the property of the form?

Private Sub InitializeControls()
 '--set control properties

 '--date picker range (this works but I don't see why I need to reference the name anymore)
 Me.DTPicker1.MinDate = Worksheets("Report365").Range("A380")
 Me.DTPicker1.MaxDate = Worksheets("Report365").Range("A14")
 Me.DTPicker2.MinDate = Worksheets("Report365").Range("A380")
 Me.DTPicker2.MaxDate = Worksheets("Report365").Range("A14")
 Me.DTPicker2.Value = Worksheets("Report365").Range("A14")


  '--date picker range (this doesn't work - invalid use of property)
 Me.DTPicker1.MinDate = Me.Worksheet.Range("A380")
 Me.DTPicker1.MaxDate = Me.Worksheet.Range("A14")
 Me.DTPicker2.MinDate = Me.Worksheet.Range("A380")
 Me.DTPicker2.MaxDate = Me.Worksheet.Range("A14")
 Me.DTPicker2.Value = Me.Worksheet.Range("A14")

 

[James006]

Hello,

Not sure to fully understand your problem ...

Why can't you have a variable defined for your worksheet ...?

 

[RoryA]

How is your Property declared, and when are you trying to run that code?

 

[RawlinsCross]

Rory, I have the following public properties in my userform code module. As for the class, I've grouped all the labels in the userform together and when you click anyone of them another userform appears with a graph of that particular label. (bit of an aside)

'Option Explicit
Private msSelectedLabel As String
Private mwksWorksheet As Worksheet
Private ChartButtons() As New Chart365Class

'--public properties
Public Property Let SelectedLabel(sSelectedLabel As String)
 msSelectedLabel = sSelectedLabel
End Property

Public Property Set Worksheet(wks As Worksheet)
 Set mwksWorksheet = wks
End Property

 

[RoryA]

You need a Property Get if you want to retrieve the assigned worksheet:

Public Property Get Worksheet() As Worksheet
 Set Worksheet = mwksWorksheet
End Property

 

[RawlinsCross]

Okay, I'll try it out. Question though - do you need both "SET" and "GET" properties?

 

[RoryA]

If you want to both retrieve and set the property, then yes.

 

[RawlinsCross]

Hi Rory, still not working. If I think out the process, I declare a new instance of the userform and assign it a worksheet:

Public Sub CCD_REPORT_Click()
Worksheets("Report365").EnableCalculation = True
Worksheets("Report365").Calculate
 '--creates instance of roadmap userform, passes variables to it,
 '     then shows modeless
 
 '--declare variable frm to be a A222ALRTCCDREPORT userform object
 Dim frm As A222ALRTCCDREPORT
 
 '--create a new instance of A222ALRTCCDREPORT
 Set frm = New A222ALRTCCDREPORT
 
[B] '--pass worksheet with headers and data to public property of frm[/B]
 Set frm.Worksheet = ThisWorkbook.Sheets("Report365")
 
 '--show frm to allow user to interact
 frm.Show vbModeless
 
End Sub

Then in the userform, I use SET/GET public properties:

Option Explicit
Private msSelectedLabel As String
Private mwksWorksheet As Worksheet
Private ChartButtons() As New ChartButtonClass
'--public properties

Public Property Let SelectedLabel(sSelectedLabel As String)
 msSelectedLabel = sSelectedLabel
End Property

Public Property Set Worksheet(wks As Worksheet)
 Set mwksWorksheet = wks
End Property

Public Property Get Worksheet() As Worksheet
 Set DataWorksheet = mwksWorksheet
End Property

Then I can invoke the DataWorksheet variable in the subsequent userform initialization event? (The code below runs when the form initializes) I'm getting variable not defined still.

Private Sub InitializeControls()
 '--set control properties
 '--date picker range (this works but I don't see why I need to reference the name anymore)
 Me.DTPicker1.MinDate = DataWorksheet.Range("A380")
 Me.DTPicker1.MaxDate = DataWorksheet.Range("A14")
 Me.DTPicker2.MinDate = DataWorksheet.Range("A380")
 Me.DTPicker2.MaxDate = DataWorksheet.Range("A14")
 Me.DTPicker2.Value = DataWorksheet.Range("A14")

 

[RoryA]

That's not going to work in that order. As soon as you create a new instance, the Userform_Initialize will run, which is before you assign the worksheet. You'd need to either use a different event, such as Userform_Activate, or perhaps put any code that depends on the worksheet variable into the Property Set procedure so that it runs when you assign the worksheet object.

 

[RawlinsCross]

That worked. Read up on "initialize" vs "activate" - never appreciated the difference or the idea of instantiation. Anyhow, thanks for the head's up.