Referencing the worksheet that has previously been assigned to a userform

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
437
If I assign a userform a specific worksheet, how then do I reference that worksheet without specifically referencing the name of the worksheet?

Code:
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?

Code:
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")
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hello,

Not sure to fully understand your problem ...

Why can't you have a variable defined for your worksheet ...?
 
Upvote 0
How is your Property declared, and when are you trying to run that code?
 
Upvote 0
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)

Code:
'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
 
Upvote 0
You need a Property Get if you want to retrieve the assigned worksheet:

Code:
Public Property Get Worksheet() As Worksheet
 Set Worksheet = mwksWorksheet
End Property
 
Upvote 0
If you want to both retrieve and set the property, then yes.
 
Upvote 0
Hi Rory, still not working. If I think out the process, I declare a new instance of the userform and assign it a worksheet:

Code:
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:

Code:
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.

Code:
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")
 
Upvote 0
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.
 
Upvote 0
That worked. Read up on "initialize" vs "activate" - never appreciated the difference or the idea of instantiation. Anyhow, thanks for the head's up.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top