Hi Frank,
The problem was they have to manually update the list.
That's why I didn't mention named list, because I thought
it would be too messy but since you've got it mostly worked
out -- all that's really needed is an Event macro to keep
the list of worksheetnames up-to-date.
Hi DaveWar,
The downside of making things easy for the user, is making
it more difficult to maintain. So would suggest putting
some comments on the "ListOfSheets" worksheet.
Place the following event macro
into "ThisWorkbook" at the end of "Microsoft
Excel Objects" (before the list of sheetname)
in the Visual Basic Editor (F11, Ctrl+R)
will update the ranges and create the wsList
defined name. You must have a worksheet
named "ListOfSheets".
The list will be updated everytime the user selects
(activates) a worksheet. It will not include a
worksheet named "Master" nor one named
"ListOfSheets".
Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim i As Long
Dim wkSheet As Worksheet
With Sheets("ListOfSheets")
.Range("A:A").Clear
i = 0
For Each wkSheet In Application.Worksheets
If LCase(wkSheet.Name) <> "master" _
And wkSheet.Name <> .Name Then
i = i + 1
.Cells(i, 1) = wkSheet.Name
End If
Next wkSheet
ActiveWorkbook.Names.Add _
Name:="wslist", RefersTo:=.Range("A1:A" & i)
End With
End Sub
More information on Event macros on my page
http://www.mvps.org/dmcritchie/excel/event.htm
