PDA

View Full Version : Solved: Naming a worksheet for all subs in project



sassora
07-01-2012, 03:37 AM
Hi

Say I have a public variable which is a worksheet. How can I set the value so that it sticks for all sub procedures in the module / project?

The following doesn't work:
Public shWorkitems As Worksheet

Sub x()

Set shWorkitems = Sheets("Job List")

End Sub


Sub y()

MsgBox shWorkitems.Name


End Sub

GTO
07-01-2012, 05:23 AM
That should work fine, excepting that I would suggest qualifying with what workbook .Worksheets("my sheet name") belongs to:
Option Explicit

Public shWorkItems As Worksheet

Sub x()
Set shWorkItems = ThisWorkbook.Worksheets("Sheet4")
End Sub

Sub y()
MsgBox shWorkItems.Name
End Sub

Your code and the variable declaration should be in a Standard Module. You don't have the code in a worksheet's module do you? Other than that, once Set, shWorkItems should be accessable unless/until the some part of the code bugs out and all variables lose their values.

sassora
07-01-2012, 05:49 AM
Thanks GTO, it is working now. I think I was running sub y without sub x and expecting shWorkItems to be automatically defined!

GTO
07-01-2012, 01:35 PM
Glad you got it working :-)

sassora
07-01-2012, 01:54 PM
:)