Excel VBA: how to access and control the "Sheet Select" dialog?

Question

When my VBA script cannot figure out which Worksheet to use, it opens an Excel dialog: "Select Sheet". This is fantastic! How do I programmatically take control and use that "Sheet Select" dialog?

I'm writing a code that uses

Application.FileDialog(msoFileDialogFilePicker)

..To set a variable = a chosen filename, but I'm also trying to set a variable to a certain Worksheet in that file. All without opening the file.

I want the workflow to be:

File Dialog opens -> Select an Excel file > OK
Worksheet Dialog opens (like Excel's "Select Sheet" dialog) -> Select one of the Worksheets in that Excel file > OK

I've been through Application.Dialog(xl...) stuff, but can't find the right option.

Please help! and thanks.

What "Select Sheet" dialog do you mean? And if you already use it in your VBA code, then you already do "programmatically take control" of it. Can you be more specific on this? Furthermore, "I'm also trying to set a variable to a certain Worksheet in that file. All without opening the file." is impossible. You can only set a variable to a Worksheet if there is an object of it - which is only the case if the file is opened. There is no way around this. — Leviathan
No the "Select Sheet" dialog looks like a combobox with all a file's worksheets in a list. It opens when Excel doesn't know which worksheet you mean to use. I was wondering if you could fill a variable with the name of a worksheet you have not opened using some kind of Dialog Worksheet picker just like you can fill a VBA variable with the name of a file you have not opened but have chosen using the msoFileDialogFilePicker dialog. — PillBoxCharger
I'm still unclear about that "Select Sheet" functionality. Do you have a concrete example (or sample code)? For the list of sheets: You have to open the file first to see what sheets are in there. Like you have to open a directory to see what files are in there. This cannot be done without opening the file. Opening the file can can be done in the background though, and the file can be closed directly afterwards. All this can be done without a user registering the file was opened at all. But it has to be opened, however invisble you try to do this for the user. — Leviathan

user3598756 user3598756 · Accepted Answer · 2016-05-05T14:26:14

you could try this (adapted form Tom Urtis's here)

Option Explicit

Dim mySht As Worksheet

Sub SelectSheet()

Dim cmdBar As CommandBar
Dim cmdBarBtn As CommandBarButton
Dim sht As Worksheet

RegDel

Set cmdBar = Application.CommandBars.Add("Register", msoBarPopup)
For Each sht In ThisWorkbook.Worksheets
    Set cmdBarBtn = cmdBar.Controls.Add
    cmdBarBtn.Caption = sht.Name
    cmdBarBtn.Style = msoButtonCaption
    cmdBarBtn.OnAction = "SelectThatSheet"
Next sht
cmdBar.ShowPopup

MsgBox "you selected sheet '" & mySht.Name & "'"

End Sub

Sub SelectThatSheet()
Set mySht = Worksheets(Application.Caller(1))
RegDel
End Sub

Sub RegDel()
On Error Resume Next
Application.CommandBars("Register").Delete
On Error GoTo 0
End Sub

Excel VBA: how to access and control the "Sheet Select" dialog?

1 Answers