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!

 

[Cosmos75]

Found another tidbit that moves you through the sheets one at a time.

Use Ctrl-Page Down and Ctrl Page Up to move through the worksheets one at a time.

The equivalent VBA code is ActiveSheet.Previous.Select or ActiveSheet.Next .Select

Hope this proves useful to you!

"With Great Knowledge Comes Great Responsibility!" - Excel-Man

 

[Cosmos75]

Re: Macro that choose which worksheet to view that is on ALL

OK, I know this has been answered before but I can't seem to find it.

If I use

Application.CommandBars("Workbook tabs").ShowPopup

then I get menu to choose which worksheet I want to activate. But if there are a lot of worksheets, it'll show the first 15 (in order that it is arranged in workbook), and the last one will be "More sheets..", which when selected brings up a a kind of listbox that list ALL the available sheets in the active workbook to choose from.

Is there code to bring that menu (listbox)?

 

[nisht]

Re: Macro that choose which worksheet to view that is on ALL

Respected Excel Guru Damon (Namaskar)

His solution:

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

Is the best solution I have seen ever for managing worksheet.

Regard to him.

http://www.pexcel.com/samplefile/sheetname/sheetnames.zip

is a sheet example file i have on my website. Added little color and a normal routine, could not resist to post here...

somebody has said. if you wanna be great you should "read between the lines."

 

[Cosmos75]

Re: Macro that choose which worksheet to view that is on ALL

With a little more searching I found this.

    x = ActiveWorkbook.Sheets.Count
    If x > 16 Then
        Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
    Else
        Application.CommandBars("Workbook Tabs").ShowPopup
    End If

I guess you can't use the "listbox" unless you have more than 16 worksheets?

 

[Cosmos75]

Re: Macro that choose which worksheet to view that is on ALL

nisht,

NICE example!

But I have no idea how that works since the code is protected.

 

[nisht]

Re: Macro that choose which worksheet to view that is on ALL

Sorry for inconviency,

YOu can download now, I have remove the protection.


Thank you for downloading.

 

[Cosmos75]

Re: Macro that choose which worksheet to view that is on ALL

nisht,

Wow!

THANK YOU!

 

[mw998]

Re: Macro that choose which worksheet to view that is on ALL

Hello All,

I'm using this code to engage the "Activate Sheet" pop-up.

Sub Select_Sheet()
'
' select_sheet Macro
'
' Keyboard Shortcut: ctrl+k
'
    If ActiveWorkbook.Sheets.Count <= 16 Then
        Application.CommandBars("Workbook Tabs").ShowPopup 500, 225
    Else
        Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
    End If
End Sub

The Activate Sheet popup appears and brings me to the desired page when I use the macro pane ALT+F8, but when I use the keyboard shortcut the Activate Sheet pane appears, but it won't take me to the selected sheet. The pane just closes.

Wondering if any of you have experienced this problem before. I've tried using several different keyboard shortcuts–it's not that.

Thanks in advanced for any help you might have to offer...

Best,
m