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

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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:
Code:
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
 
Upvote 0
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).
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top