Macro that choose which worksheet to view that is on ALL wor

[Cosmos75]

Is there a macro that choose which worksheet to view? Maybe a combo box on each page? BUT I need those combo boxes to appear automatically on each worksheet and be popoulated/updated with the worksheets automatically since this workbook is updated continually.

And YES, I know you can use the tabs at the bottom, but that can be cumbersome with a lot of worksheets.

Thanks!

[Al Chara]

How about creating a summary page...

You can download ASAP at the following link:
http://asap-utilities.com/
And create a summary sheet.

Or you can make your own. Try the following code in the Module for ThisWorkbook:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
Worksheets("Summary").Hyperlinks.Add Anchor:=Worksheets("Summary").Cells(ws.Index, 1), Address:="", SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
Next
End Sub

[Damon Ostrander]

Hi Cosmos75,

There actually is a built-in capability that is not well advertised to do this. Simply right-click on the "Workbook tabs" toolbar and a worksheet list shortcut will pop up. This toolbar is the one that shows the worksheet tabs, but you have to click on the left end of the toolbar where the go right/left buttons are (i.e., right-click on one of these buttons).

[Cosmos75]

Damon Ostrander!!

That's exactly what I wanted!! Is there code to call that from a Macro? Tried recording a macro but if only has a line to select the sheet I selected from the pop-up box.

Al Chara,

I actually have a summary page that is linked to all other worksheets. And the other worksheets are linked back to the summary page. I wanted a way to jump to any page from ANY other page. Hopefully, I can automatically add a button to each worksheet to be able to bring up that pop-up box that Damon Ostrander was talking about!

PLEASE LET THE BE CODE FOR THAT!!

Any ideas on automatically adding a button to each workshet to activate that pop-up box? (assuming there's code for that pop-up box)

[Cosmos75]

Anyone know?

[Damon Ostrander]

Hi again Cosmos,

The code:

CommandBars("Workbook tabs").ShowPopup

will pop up the shortcut menu for worksheet selection.

If you want to automatically add a button to each worksheet in your workbook that will pop up this menu, just use the following macro (AddButtons). This example adds each button in cell B4. Just change this reference (one place) to put the button in any cell you choose. It does not add buttons to other types of sheets (e.g., Chart sheets), but this would be easy to do as well.

Sub SelectWorksheetMenu()
CommandBars("Workbook tabs").ShowPopup
End Sub

Sub AddButtons()
Dim WS As Worksheet
Dim C As Range 'The cell where the button will be placed
Dim Width As Single 'The button width
For Each WS In Worksheets
Set C = WS.[B4] 'put button in cell B4
If C.Width > 60 Then Width = C.Width Else Width = 60
With WS.Buttons.Add(C.Left, C.Top, Width, C.Height)
.OnAction = "SelectWorksheetMenu"
.Characters.Text = "Select Sheet"
.Characters.Font.Size = 8
End With
Next WS
End Sub

[Cosmos75]

Guru Damon Ostrander!!!!

THANK YOU!!!!

[Al Chara]

Since you have a summary page, why don't you put a combobox on each page with the ListFillRange equal to Summary!A:A. Then use the following code to activate the selected worksheet:

Private Sub ComboBox1_Change()
Worksheets(ComboBox1.Value).Select
End Sub

Edit as needed and post with further questions.

[Cosmos75]

Al Chara,

That sounds like a good idea. The only thing is I need the combo-box to show up on every page and pages are added regularly. SO, I'd have to figure out how to automatically add a combo box to each sheet (except summary sheet) and I'm not very good at VBA so far.

Also, would your code be more efficient or would Damon's be more efficient?

[Cosmos75]

Damon,

One question, if I have to click add button macro every time I add a sheet, the sheets that already have a button will have an extra button on top of the existing button.

Is there a way to check if a button already exists as to not have a redundant button?

I apologize about this. I don't mean to sound ungrateful cause believe me I AM VERY GRATEFUL for all the help.