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 Ill 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 dont have much experience with vba therefore any help to a novice would
be greatly appreciated.

Many thanks in advance €“ Tom.

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

"tomhelle" skrev i meddelelsen
...
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 Ill 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 dont have much experience with vba therefore any help to a novice would
be greatly appreciated.

Many thanks in advance €“ Tom.