switching between multiple sheets

S

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?
 
D

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top