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. sam: You lost me. I don’t know what the “More Sheets” dialog is. Did you mean “Move Sheets”?

3. 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

4. Brilliant – many thanks.

5. 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

6. 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

7. The “Previous Page” links on tag-archive pages do not work, can you please fix them? Thanks!

   Example:
   http://www.dailydoseofexcel.com/page/2/?category_name=vba%2Fuserforms-and-controls%2F

8. A further observation:

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

   1.

9. 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?

10. You can also use findcontrol to call it like I do in the sample add-in

    See
    http://www.contextures.com/xlToolbar01b.html

11. @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.