Macro to create validation list of worksheet names

[tomhelle]

I have a large workbook consisting of a “menu†worksheet and many worksheet
templates. On the “menu†worksheet, I want to enable the user to select the
particular worksheets they need for their job from dropdown lists contained
in cells B1:B25. The names of the worksheet “templates†are subject to
change. In other words, the user may add a new duplicate template worksheet
to the workbook or they may change the names of any of the existing
templates. On the “menuâ€, I want a macro that will create a dropdown list in
each of cells B1 through B25 that includes all the current worksheet names in
the workbook except for a selected few that I’ll call “menuâ€, “Worksheet Xâ€,
“Worksheet Yâ€, and “Worksheet Zâ€. If possible, I would like to have the user
be able to hyperlink from their dropdown list to the selected worksheets.

I don’t have much experience with vba therefore any help to a novice would
be greatly appreciated.

Many thanks in advance – Tom.

 

[Per Jessen]

Hi Tom

Look at this:

Sub Menu()
Dim Menu As Worksheet

Set Menu = Worksheets("Menu")

For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Menu" And sh.Name <> "SheetX" Then
Menu.Hyperlinks.Add anchor:=Menu.Range("B1").Offset(off),
Address:="", _
SubAddress:="'" & sh.Name & "'!A1", TextToDisplay:=sh.Name
End If
off = off + 1
Next
End Sub

Regards,
Per