Showing Hidden Sheet and Workbooks Dialog in VBA

A tip from Scott:

Windows – Unhide, from the menu, shows the Unhide dialog box for unhiding workbooks.

In vba, Application.Dialogs(xlDialogUnhide).Show gets the job done.

Where it gets tricky is unhiding worksheets. Via the menu, Format – Sheets – Unhide

In vba, the name of the dialog is not so intuitive.

Application.Dialogs(xlDialogWorkbookUnhide).Show

Poorly named, for sure, but you’ve been warned. Thanks Scott.

11 thoughts on “Showing Hidden Sheet and Workbooks Dialog in VBA

  1. Try this

    1 Open a blank file
    2 Insert 20 new sheets (make sure you have more than 15 sheets in the file)

    3. Write code to display the “Activate Dialog” – The one thats displayed when you right click on the VCR controls and click on “More Sheets”

  2. If you’re looking for code to display the sheet list this works.

    Sub ShowSheetList()

    On Error Resume Next
        Select Case ActiveWorkbook.Sheets.Count
            Case Is <= 16
                Application.CommandBars(“Workbook Tabs”).ShowPopup 600, 350  ‘From Left, From Top
           Case Else
                If Application.CommandBars(“workbook tabs”).Controls(16).Caption Like “More Sheets*” Then
                    Application.SendKeys “{END}~”
                End If
                Application.CommandBars(“workbook tabs”).ShowPopup 600, 350
        End Select

    End Sub

  3. An interesting aside to the Worksheets unhide dialog box :

    If there are no hidden sheets in the workbook,
    and, you use the menu command Alt + O + H + U, it does nothing (there’s nothing to unhide)
    but, if you use the VBA macro, it displays a dialog box with a blank list !

    ALSO, if you have hidden the sheet in the VBE editor with the sheet property ‘xlSheetVeryHidden’
    and, you use the menu command Alt + O + H + U, it does nothing (because of the VeryHidden property)
    but, if you use the VBA macro, it displays the dialog box with the VeryHidden sheet !

    Khushnood

  4. I’ve been using the (xlDialog-xxxx).Show trick for a while now.
    They’re part of my Personal.xlsb file with keyboard shortcuts assigned to them or added to the QAT.

    Some samples which might be useful :

    Works in Excel 2003 and after
    Application.Dialogs(xlDialogSelectSpecial).Show
    – you can write an On Error condition to trap possible errors where ‘No cells were found’ (e.g. blanks, formulas, etc.)
    Application.Dialogs(xlDialogColumnWidth).Show
    Application.Dialogs(xlDialogPasteSpecial).Show
    Application.Dialogs(xlDialogConditionalFormatting).Show
    (these may work in earlier versions too; but not sure, and can’t check)

    Works in Excel 2003 and before
    Application.Dialogs(xlDialogAttachToolbars).Show – for developers who keep making changes to custom toolbars attached to workbooks.

    To check for other possible dialogs, type upto “Application.Dialogs(xlDialog” and then press Ctrl + SpaceBar.
    The valid options will display in a drop-down list.
    ALL the options may not work, though (some of them may not be supported by the object Dialogs)

    Khushnood

  5. A further observation:

    Given how useful this was, it got me thionking, so I wrote 3 macros as follows (Excel 2003):

    1.

  6. Oops

    As I was saying:

    1. Show all worksheets and allow selection of 1 or many and unhide them (regardless of whether they are hidden or very hidden)

    2. Show all visible worksheets and allow selection as per 1 above to make them Hidden
    3. as 2 above, but to make them VeryHidden

    Might be overkill, but it will be useful when I get large workbooks.

    Thanks are due to John Walkenbach because I utilised an old process of his that creates a dialogsheet on teh fly, together with checkboxes.

    That was about the limit of my capability, but it would be good to know how I could update that part of the process to make it compatible with Excel 2003 and 2007?

  7. @Sam
    I tried it and it lists the open (and other) workbooks e.g. PERSONAL.XLSB, SOLVER etc instead of Worksheets!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.