Drop down menu for navigating between worksheets

[Bevonius]

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

 

[Guest]

this should get you started:

Insert a new sheet and name it: MySheets
Create a dynamic range:
Insert>Name>Define 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

 

[John]

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

 

[bowe]

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]

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!

this should get you started:

Insert a new sheet and name it: MySheets
Create a dynamic range:
Insert>Name>Define 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

 

[John]

My pleasure

John

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!

 

[huntermcg]

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

 

[huntermcg]

.... 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]

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

(read former 2 posts of mine).

 

[Guest]

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]

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

 

[huntermcg]

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.

 

[Guest]

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]

please attach a sample sheet to this post or mail me the example to:
(e-mail address removed)

thanks in advance.

best regards, hunter

 

[huntermcg]

I found a better way
http://www.excelforum.com/newreply.php?do=newreply&noquote=1&p=135877