CODEWITHSUNDEEP
vbaexcel
Rosetta
Rosetta

Reputation: 2725

Invoking the Activate dialog for sheets

I want to invoke the Activate dialog for sheets in a workbook such as below. This dialog can be called manually by right clicking on the arrow button at the bottom left of Excel (2013).

enter image description here

I tried this:

Application.Dialogs(xlDialogActivate).Show

But instead of showing the list of sheets, it shows the list of open workbooks:

enter image description here

How do I call the Activate dialog for sheets?

Upvotes: 1

Views: 1412

Answers (2)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

An alternative is to show the Sheet Command bar:

Sub ShowSheets()
    Application.CommandBars("Workbook tabs").ShowPopup
End Sub

enter image description here

Content salvaged from comments by Scott Holtzman and Davesexcel.

Upvotes: 0

johankr
johankr

Reputation: 170

You could create your own dialog box if you want. Create a userForm and populate it with the names of the Worksheets upon activation. You can see what the user selected through the selected function, e.g.

ListBox1.Selected(i)

You can then call a sub with the name of the Sheet and activate it, e.g.

Sub ChangeSheet(SheetName)
    Worksheets(SheetName).Activate
End Sub

Upvotes: 1

Related Questions