Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a worksheet object on Open that doesn't die?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a worksheet object on Open that doesn't die?
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" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set a worksheet object on Open that doesn't die?
Wow!! Thanks so much, Chip. This is great!
Ed On Sep 5, 11:24 am, "Chip Pearson" wrote: 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 Consultingwww.cpearson.com (email on the web site) "Ed from AZ" wrote in oglegroups.com... 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Charts and Charting in Excel | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Discussion (Misc queries) | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Worksheet Functions | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Programming | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | New Users to Excel |