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

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

Thanks for your effort John :) There seems to something fishy though.
It took a while before i remembered that commas don't work but
semicolon does (specific for the Swedish version?) so I changed from
e.g. "=INDEX(SheetList*,*B1)" to =INDEX(SheetList*;*B1) same for the
OFFSET.

There is still some problem

The control doesn't accept SheetList in the input box, it says "Invalid
reference". I tried to circumnavigate this by defining an area e.g.
A1:A10 and then I get the values into the list.

But when I select a value in the list I get "Incopatible types" in row
"ShName = Range("GoToSheet")". It seems that there is someting wrong
with SheetList that is inherited all the way.

I tried to fiddle around a little more and discovered, shouldn't COUNTA
be used instead of COUNT since the sheet names are text values and not
numbers.

It seems so close but still so far away :(


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

Sorry cush, I read the wrong name when I was writing my reply. Al
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
could use english function names but for some reason this version o
EXCEL only accepts Swedish names. So I had to change COUNTA to ANTAL
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 ne
sheet.
Run this code first.

The second macro will activate the sheet that is selected in th
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 dro
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



--
bow
-----------------------------------------------------------------------
bowe's Profile: http://www.excelforum.com/member.php...fo&userid=2711
View this thread: http://www.excelforum.com/showthread.php?threadid=46912

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

I tried to make this menu too but still get the errors bowe referre
to.

If I put SheetList into the input box I get invalid reference.
And If I put in the range I get an error (1004) executing an item i
the list.

I guess I still have to put Sheetist into the inputbox, but do not kno
what I am doing wrong?

Also, did I understand it right I have to make in column A a list o
hyperlinks to the sheets?

Please help, I wanna make it work too.

Best regards,
Hunte

--
huntermc
-----------------------------------------------------------------------
huntermcg's Profile: http://www.excelforum.com/member.php...fo&userid=1939
View this thread: http://www.excelforum.com/showthread.php?threadid=46912

.... read my former post please too.

I understood what I wrote in the former post, was not right about
putting in a list op hyperlinks in the first column. I understand now I
had to run the macro MakeTabList first. That I did and made the list in
the column A.

I assigned the drop downbox to the other macro now 'GoToSheet'.

I only get a '400' error ? I do not know what this is.

I guess It has to do with the input box in the foprmat of the drop down
box. I still get a Invalid reference when I put in 'SheetList'there.

So again, please help on giving me the final solution ..


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

If it's not strictly necessary for you to have this on the worksheet, you
can always right-click on the worksheet navigation buttons in the bottom
left-hand corner.

Best regards

John

"Bevonius" wrote in
message ...

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