switching between multiple sheets

[SBS user]

I currently have an excel file that has around 30 sheets that I need to
switch between very often. When I right click on the arrows that control
the sheets it shows me a list of 15 of the sheets as well as an option for
more sheets. Is there a way to change the amount of sheets it shows in that
list so I can change it to something like 30?

 

[Roger Govier]

Hi

No, you can't.
but try something like Dave Peterson's navigation aid
http://www.contextures.com/xlToolbar01.html

 

[Dave Peterson]

You could use a macro like:

Option Explicit
Sub testme()

On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
If Err.Number <> 0 Then
Err.Clear
Application.CommandBars("Workbook Tabs").ShowPopup
If Err.Number <> 0 Then
Err.Clear
Beep
End If
End If
On Error GoTo 0

End Sub

 

[Héctor Miguel]

hi, !

I currently have an excel file that has around 30 sheets that I need to switch between very often.
When I right click on the arrows that control the sheets it shows me a list of 15 of the sheets as well as an option for more sheets.
Is there a way to change the amount of sheets it shows in that list so I can change it to something like 30?

not directly (I guess) and FWIW, given that the control's caption is constrained by localized installed language
any instruction for controls .Caption like <cBar>.Controls("more sheets...") would fail for non-english installation
but you could make a twist and assign a shortcut to a macro like...

Sub myShList()
With Application.CommandBars("workbook tabs").Controls(16)
If Right(.Caption, 3) = "..." Then .Execute Else .Parent.ShowPopup
End With
End Sub

this would also fail *IF* the user customize it's "workbook tabs" controls(16) -index/position-

hth,
hector.

 

[SBS user]

Thanks to everyone for the help on this