ExcelBanter - View Single Post - Drop down menu for navigating between worksheets

John[_88_]

My pleasure :)

John

"bowe" wrote in message
...

Sorry cush, I read the wrong name when I was writing my reply. All
credits to you cush.

I discovered the problem:
- I have to use ";" instead of ","
- COUNTA instead of COUNT
- This took some time, I have a new computer and with the old one I
could use english function names but for some reason this version of
EXCEL only accepts Swedish names. So I had to change COUNTA to ANTALV
and OFFSET to FÖRSKJUTNING

But most important, now it works! :)

Thanks a million cush!

cush Wrote:
this should get you started:

Insert a new sheet and name it: MySheets
Create a dynamic range:
InsertNameDefine and type in the name SheetList
In the RefersTo box type:
"=Offset(MySheets!$A$1,0,0,Count(MySheets!$A:$A),1 )
Click ok

In this new sheet, select C1.
Paste in this formula:
=INDEX(SheetList,B1)
Name this cell: GoToSheet

On this new sheet, create a new dropdown from the Forms menu.
Rt click on it and select Format the control.
In the Input box type: SheetList
In the Cell Link box type: MySheets!B1
click ok

Copy and paste the following code into a vba module:
The first sub will create a list of worksheets in column A of this new
sheet.
Run this code first.

The second macro will activate the sheet that is selected in the
drop-down.

Sub MakeTabsList()
Dim Sh As Worksheet
Dim i As Integer
For Each Sh In Worksheets
Sheets("MySheets").Range("A1").Offset(i) = Sh.Name
i = i + 1
Next Sh
End Sub

Sub GotoSheet()
Dim ShName As String
ShName = Range("GoToSheet")
Sheets(ShName).Activate

End Sub

Now we need to connect the dropdown to the 2nd macro:
Rtclick the dropdown.
click on Assign Macro
select GoToSheet
click ok

This should take you to the worksheet that you selected.
Likely you will want to have this dropdown on all sheets so you
can easily navigate with your dropdown, rather than clicking
on the TABS.
So: copy and paste the dropdown onto each of your sheets.
Hope this helped

"Bevonius" wrote:

I have been searching the forums but I don't seem to hit the correct
search criterias, please help

I need to create a drop down menu on one of my worksheets. The list
should contain all the woksheet/tabs in the workbook and allow me to
navigate to any sheet. When a new sheet is added it should also be
added to the drop down menu.

Could you please describe it step by step from creating the drop
down
to the macro since I am a at programming Excel

--
Bevonius

------------------------------------------------------------------------
Bevonius's Profile:
http://www.excelforum.com/member.php...o&userid=27393
View this thread:
http://www.excelforum.com/showthread...hreadid=469122

--
bowe
------------------------------------------------------------------------
bowe's Profile:
http://www.excelforum.com/member.php...o&userid=27110
View this thread: http://www.excelforum.com/showthread...hreadid=469122