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

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

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

Is there any PRO who can help me with this ??

(read former 2 posts of mine).


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

I couldn't get the above to work either so here is what I did:

Create sheet called MySheets
In cell C1 (on MySheets) type "=CONCATENATE("'MySheets'!A1:A",COUNTA(A:A))"

Place the following code in a module:
Sub OpenUp()
Call MakeTabsList
Call FillListFillRange
End Sub

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 FillListFillRange()
Sheets("Admin - Forms").ComboSheets.ListFillRange = _
Sheets("MySheets").Range("C1").Text
End Sub

Sub GotoSheet()
Dim ShName As String
ShName = Sheets("MySheets").Range("B1")
Sheets(ShName).Activate
Sheets("MySheets").Range("B1").Value = "Select a sheet to jump to"
End Sub

On the sheet of your choice place a combobox and commandbutton (I've named
them ComboSheets and ButtonGoToSheet). in the code for that sheet place the
following:

Private Sub ButtonGoToSheet_Click()
Call GotoSheet
End Sub

Now in the workbook_activate () event place the following line:
Call OpenUp

That should do it.

"huntermcg" wrote:


Is there any PRO who can help me with this ??

(read former 2 posts of mine).


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

Could you attach an example sheet in a reply post because I still canno
figure it out? That would be very helpfull

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

Create sheet called MySheets
In cell C1 (on MySheets) type
"=CONCATENATE("'MySheets'!A1:A",COUNTA(A:A))"

This does not work on my sheet.

I edited counta into count and the , into ;

also what do i exactly do with:
Now in the workbook_activate () event place the following line:
Call OpenUp

I do not reaally understand what you mean by that ? I am no VBA pro.

Please answer me soon and if possible attach a simple example sheet.


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

The purpose of the concatenate function is to make a text string that the
ListFillRange of the combobox will accept. in my case, I have 29 sheets so
the concatenate function returns 'MySheets'!A1:A29

The reason I used counta is because this function counts non numerical
items. If the concatenate function isn't working, use the wizard to create
it. that helps a lot sometimes.

When you open the VBa editor double click on "ThisWorkbook" to open the code
window. You will see two drop down lists. in the one on the left select
workbook. then in the other select activate.

Then in the Private sub that appears type Call OpenUp

It should appear as:
Private Sub Workbook_Activate()
Call OpenUp
End Sub

What this does is call the procedure called 'OpenUp' that we created
previously.

Let me know if you have any other questions.


"huntermcg" wrote:


Create sheet called MySheets
In cell C1 (on MySheets) type
"=CONCATENATE("'MySheets'!A1:A",COUNTA(A:A))"

This does not work on my sheet.

I edited counta into count and the , into ;

also what do i exactly do with:
Now in the workbook_activate () event place the following line:
Call OpenUp

I do not reaally understand what you mean by that ? I am no VBA pro.

Please answer me soon and if possible attach a simple example sheet.


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

please attach a sample sheet to this post or mail me the example to:


thanks in advance.

best regards, hunter


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

I found a better way
http://www.excelforum.com/newreply.p...ote=1&p=135877

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