Unhide sheet macro

The UK's most regular instructor-led training courses.
Training information: excel+and+powerpoint+courses+uk · Excel training london · Microsoft Excel Training UK
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · High Priority · Version Standard

Unhide sheet macro

Nick has attended:
Access Introduction course

by - delegate Nick bronze contributer [28 posts] (2008 Apr 1 Tue, 10:57) Reply

I have recorded a macro so that I can hide and unhide sheets within a particulr workbook. However, whenever I delete a sheet, add a sheet or change the name of a sheet the macro fails and requires updating.

Is it possible to record a macro that will hide or unhide sheets even when new sheets are added, deleted or their names are changed?

Cheers
NICK

Version	Date	Location	Places available	Book	Next place rate:
Excel VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2008 Nov 25 Tue + 26 Wed	Bayswater	6	book now	£455	£515
2003	2008 Nov 26 Wed + 27 Thu	Bloomsbury	2	book now	£495	£495
2003	2008 Dec 1 Mon + 2 Tue	Bloomsbury	2	book now	£495	£495
2003	2008 Dec 4 Thu + 5 Fri	Bloomsbury	5	book now	£450	£485
2007	2008 Dec 11 Thu + 12 Fri	Bayswater	5	book now	£475	£515
2007	2009 Jan 8 Thu + 9 Fri	Bloomsbury	6	book now	£450	£475
Full Schedule: See all 37 Excel VBA course dates. Bookings currently available until 26th November 2009.

RE: Unhide sheet macro

by - trainer Mara silver contributer [73 posts] (2008 Apr 7 Mon, 06:10) Reply

Hello Nick,

Thank you for your post. I have tried to replicate this issue, but I do not get the same problem. I would be interested in seeing the VBA for this macro. Could you please press ALT+F11, find the folder on the left side called "Modules" and find the module that has the particular macro? If you could send me the code that appears, it may help me better understand why this is failing for you.

Have a great day.

Regards,
Mara
Microsoft Office Specialist Master Trainer

RE: Unhide sheet macro

by - delegate Nick bronze contributer [28 posts] (2008 Apr 7 Mon, 11:34) Reply

Mara

To recap, looking to write a macro to unhide any hidden sheets in a workbook.
See the macro below (I have first hidden all sheets then unhidden all sheets) - again, the macro is fine until I decide to change a name, add or delete a sheet - when I do this I have to manually update the macro for any changes made to sheets or sheet names.

Cheers
NICK

Sub Unhide()
'
' Unhide Macro
' Macro recorded 07/04/2008 by nickm
'
' Keyboard Shortcut: Ctrl+u
'
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", _
"Sheet8", "Sheet9", "Sheet10")).Select
Sheets("Sheet1").Activate
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet6").Visible = True
Sheets("Sheet7").Visible = True
Sheets("Sheet8").Visible = True
Sheets("Sheet9").Visible = True
Sheets("Sheet10").Visible = True
Sheets("NOTES").Select
Range("B2").Select
End Sub

RE: Unhide sheet macro

by - trainer Mara silver contributer [73 posts] (2008 Apr 21 Mon, 01:27) Reply

Hi Nick,
You will indeed have some issues if you rename your sheets, because the macro is looking for sheets with names like "Sheet1". If you edit the VBA and change the sheet names there, it should work.

Please let me know if this did the trick for you.

Regards,
Mara

· 3 Reasons why Your Small Business Office Needs Microsoft Excel Training
· Excel Advanced Courses Ease the Frustration of Learning Complex Tasks
· Reporting and Analysis with Excel
· From the Classroom to the Corporate World
· 3 Reasons Why Excel Training Courses For Employees Can Improve Your Business

Please browse our web site to find out more about
excel+and+powerpoint+courses+uk and other Microsoft training courses.