Unhide Multiple Sheets at once

[stef]

Excel 2002 SP3
Win XP HE

Hi,

I know how to hide multiple worksheets at once, but cannot seem to find
a way to unhide *multiple* sheets at once.

Anyone?

 

[Gord Dibben]

stef

Only through code.

Sub unhide()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[stef]

Gord,
Beautiful,
Thanks a lot!

stef

Only through code.

Sub unhide()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Thanks for the feedback.

Gord

Gord,
Beautiful,
Thanks a lot!

 

[Khalil Handal]

I am interested in protecting or unprotecting several worksheets that has
the same password. How can this be done using the VBA code and where to put
the code?

 

[Gord Dibben]

Khalil

Here are 4 macros.

You can store them in a module in a newly created workbook which you save as an
Add-in or in your Personal.xls.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="justme"
Next ws
End Sub

Sub UnProtect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.UnProtect Password:="justme"
Next ws
End Sub


Gord Dibben MS Excel MVP

 

[Khalil Handal]

Personal.xls is something that I don't know about???? Feww more line in
breif will help.
Thanks

 

[Gord Dibben]

Personal.xls is a workbook where you can store macros that will be available for
all open workbooks. See help for more details.

If you don't have one created, go to Tools>Macro>Record new macro.

When the dialog appears, in the "store macro in", select Personal Macro
Workbook.

Record a few steps like copying A1 to B1 then stop recording.

You now have a Personal.xls.

Window>Personal.xls>Hide.

Close Excel and save changes to Personal.xls when asked.

Re-open Excel and Personal.xls will open hidden in the background.

To access it, hit Alt + F11 to go to VBE.

Select Personal.xls and double-click on Module1 to open.

Copy/paste the macros into that module.

When closing Excel save Personal.xls when asked.


Gord

 

[Khalil Handal]

Thanks

Personal.xls is a workbook where you can store macros that will be
available for
all open workbooks. See help for more details.

If you don't have one created, go to Tools>Macro>Record new macro.

When the dialog appears, in the "store macro in", select Personal Macro
Workbook.

Record a few steps like copying A1 to B1 then stop recording.

You now have a Personal.xls.

Window>Personal.xls>Hide.

Close Excel and save changes to Personal.xls when asked.

Re-open Excel and Personal.xls will open hidden in the background.

To access it, hit Alt + F11 to go to VBE.

Select Personal.xls and double-click on Module1 to open.

Copy/paste the macros into that module.

When closing Excel save Personal.xls when asked.


Gord