switching between multiple sheets

  • Thread starter Thread starter SBS user
  • Start date Start date
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?
 
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
 
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?
Click to expand...

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.
 
Back
Top