Select Sheet Dialog Box

chris-evans

Board Regular
Joined
Feb 19, 2002
Messages
73
I am wanting to launch the Excel dialog box that enables the user to select a sheet to view via VBA.

I know there is one built into Excel as if you have a file open with many sheets you and you right click on the scroll right or left buttons for the sheets and then select ....more sheets it appears.

Could someone tell me the name of this dialog box?

Many thanks

Chris
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It's not a dialog box. It's a CommandBar of type msoBarTypePopup named "Workbook tabs".
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
Forgive the intrusion...not sure if Mr. Poulsom is still on line.

To answer your question, one way is to use a workbook level event to call a standard VBA macro that is triggered from selecting an option (in your case a sheet tab name) off the pop-up command bar. Thanks to Hans Herber for the basic direction of the following code, which I modified slightly to do what you want. You can find this and ~1600 other interesting examples on the CD being advertised for sale through this site.

The order of sheets listed on the pop-up menu will depend on their index number (the order their tabs appear in your workbook).

In a standard VBA module, insert this:

Sub GoToSheet()
On Error Resume Next
Worksheets(Application.Caller(1)).Select
CommandBars("Register").Delete
End Sub

'''''''''''''''''''''''''''''''''''''''''

In the workbook module, insert this:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim cReg As CommandBar
Dim cbb As CommandBarButton
Dim ws As Worksheet
On Error Resume Next
Application.CommandBars("Register").Delete
On Error GoTo 0
Set cReg = Application.CommandBars.Add("Register", msoBarPopup)
For Each ws In ThisWorkbook.Worksheets
Set cbb = cReg.Controls.Add
cbb.Caption = ws.Name
cbb.Style = msoButtonCaption
cbb.OnAction = "GoToSheet"
Next ws
cReg.ShowPopup
End Sub

'''''''''''''''''''''''''''''''''''''''''

Now, right click on any cell in any sheet to get a list of sheets in the workbook that you can select and go to.

Is this what you want?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here is another solution using a User Form:

Code:
Option Explicit
Option Base 1

Private Sub UserForm_Initialize()
    Dim ShList()
    Dim ShCount As Integer
    Dim x As Integer
    Dim ListPos As Integer
    ShCount = ActiveWorkbook.Sheets.Count
    ReDim Preserve ShList(1 To ShCount)
    For x = 1 To ShCount
        If Sheets(x).Name = ActiveSheet.Name Then
            ListPos = x - 1
        End If
        ShList(x) = Sheets(x).Name
    Next x
    With ListBox1
        .List = ShList
        .ListIndex = ListPos
    End With
End Sub

Private Sub CommandButton1_Click()
    Sheets(ListBox1.Value).Activate
    Unload UserForm1
End Sub

Insert a User Form (UserForm1) and add a List Box (ListBox1) and a Command Button (CommandButton1). Then paste the code into the UserForm module.
 

mwoollen

New Member
Joined
Oct 26, 2011
Messages
1
In a standard VBA module, insert this....

.... Is this what you want?

Tom, interesting stuff... The code works and I say neat-o! However, I'm guessing that this code will not be desirable by many because you've overwritten the standard right-click functionality. However, cool stuff! Thanks for sharing!
 

Forum statistics

Threads
1,148,173
Messages
5,745,183
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top