Unhide Multiple Sheets at once

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stef - 24 Jun 2007 00:20 GMT

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?

Reply to this Message

Gord Dibben - 24 Jun 2007 00:32 GMT

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

>Excel 2002 SP3
>Win XP HE
[quoted text clipped - 5 lines]
>
>Anyone?

Reply to this Message

stef - 24 Jun 2007 01:08 GMT

Gord,
Beautiful,
Thanks a lot!

> stef
>
[quoted text clipped - 40 lines]
>>
>> Anyone?

Reply to this Message

Gord Dibben - 24 Jun 2007 01:19 GMT

Thanks for the feedback.

Gord

>Gord,
>Beautiful,
[quoted text clipped - 44 lines]
>>>
>>> Anyone?

Reply to this Message

Khalil Handal - 24 Jun 2007 07:35 GMT

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?

> stef
>
[quoted text clipped - 41 lines]
>>
>>Anyone?

Reply to this Message

Gord Dibben - 24 Jun 2007 15:33 GMT

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

>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
[quoted text clipped - 45 lines]
>>>
>>>Anyone?

Reply to this Message

Khalil Handal - 24 Jun 2007 21:02 GMT

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

> Khalil
>
[quoted text clipped - 91 lines]
>>>>
>>>>Anyone?

Reply to this Message

Gord Dibben - 24 Jun 2007 22:16 GMT

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

>Personal.xls is something that I don't know about???? Feww more line in
>breif will help.
[quoted text clipped - 95 lines]
>>>>>
>>>>>Anyone?

Reply to this Message

Khalil Handal - 25 Jun 2007 05:43 GMT

Thanks

> Personal.xls is a workbook where you can store macros that will be
> available for
[quoted text clipped - 132 lines]
>>>>>>
>>>>>>Anyone?

Reply to this Message