xlDialogs - Sheet Name List

[Guest]

When you right click on the 'next or previous' sheet arrows a list of sheet
names appears which enables the user to jump to a specific sheet or if
there's a large amount of sheets then you have the option to view 'more
sheets...'

The dialog box that appears has the title of Activate, but there isn't a
xlDialog called Activate.

Can you tell me what it's called, or the code to actually show it?

Thanks

 

[Norman Jones]

Hi Trevor,

Try:

Sub ShowSheetList()
'Based on code posted by Jim Rech
On Error Resume Next
If ActiveWorkbook.sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
On Error GoTo 0

End Sub

 

[Guest]

Thanks Norman, I'll give it a go.

Trevor

Hi Trevor,

Try:

Sub ShowSheetList()
'Based on code posted by Jim Rech
On Error Resume Next
If ActiveWorkbook.sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
On Error GoTo 0

End Sub