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,
<span class="vb">Application.Dialogs(xlDialogUnhide).Show</span>
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.
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”
sam: You lost me. I don’t know what the “More Sheets” dialog is. Did you mean “Move Sheets”?
If you’re looking for code to display the sheet list this works.
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
Brilliant – many thanks.
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
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
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
A further observation:
Given how useful this was, it got me thionking, so I wrote 3 macros as follows (Excel 2003):
1.
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?
You can also use findcontrol to call it like I do in the sample add-in
See
http://www.contextures.com/xlToolbar01b.html