Set a worksheet object on Open that doesn't die?

[Ed from AZ]

I am creating a "template" workbook. When done, it will have a
"splash" worksheet, a "tools" worksheet with buttons for different
macros, and a "list" worksheet. A macro in the "MASTER" workbook
pulls up this template and adds the "list" worksheet depending on the
project, so the name of the "list" worksheet is never the same. All
of the "tools" macros, though, need to work on this "list" worksheet.

Is there code I can put in the Workbook_Open event that will set a
reference or object to this "list" worksheet that can be referenced by
the macros as long as the workbook is open?

Ed

 

[Chip Pearson]

If the List worksheet doesn't exist when the workbook is first created,
you'll need to use a NewSheet event to execute when a sheet is added. You
could then store the reference to the newly created sheet in a Public
variable and also in a defined name. Then, on subsequent opens, test for the
existence of the defined name, and if it exists, set the variable to the
worksheet whose name is stored in the defined name. This assumes that the
first worksheet that is added to the workbook is the List worksheet.

In a code module, use

Public ListWorksheet As Worksheet

Public Function NameExists(WhatName As String) As Boolean
On Error Resume Next
NameExists = CBool(Len(ThisWorkbook.Names(WhatName).Name))
End Function

Public Function SheetFromName(WhatName As String) As Worksheet
Dim WS As Worksheet
Dim S As String
On Error GoTo ErrHandler:
If NameExists(WhatName:=WhatName) = True Then
S = ThisWorkbook.Names(WhatName).RefersTo
S = Mid(S, 3, Len(S) - 3)
Set SheetFromName = ThisWorkbook.Worksheets(S)
End If
Exit Function
ErrHandler:
Set SheetFromName = Nothing
End Function

In the ThisWorkbook module, use

Private Sub Workbook_NewSheet(ByVal Sh As Object)
If NameExists("ListWorksheet") = False Then
ThisWorkbook.Names.Add Name:="ListWorksheet", RefersTo:=Sh.Name
Set ListWorksheet = Sh
Else
Set ListWorksheet = SheetFromName("ListWorksheet")
End If
End Sub

Private Sub Workbook_Open()
If NameExists("ListWorksheet") = True Then
Set ListWorksheet = SheetFromName("ListWorksheet")
End If
End Sub

When the workbook is opened the first time, nothing is done with
ListWorksheet and it will reference Nothing. When the List worksheet is
added,
ListWorksheet will refer to that worksheet *IF* the defined name
"ListWorksheet" does NOT exist. When sheet is added, or the workbook is
open
subsequently, the "ListWorksheet" Name will contain the name of the List
worksheet and the ListWorksheet variable will reference the List worksheet.

You can then use the ListWorksheet variable anywhere in your code.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Ed from AZ]

Wow!! Thanks so much, Chip. This is great!

Ed