Unhide hidden/protected worksheets

D

davegb

Well, now I've done it! I was experimenting writing a macro to hide
some of the sheets in a workbook, and exclude others. It did just the
opposite of what I wanted - hid the ones I didn't want hidden. I
modified it to hide the others, knowing it would leave at least one
unhidden, which it did. But when I tried to modify the macro to unhide
some or all of the sheets, it hangs up.


Sub RecShtUnHide()
Dim wkSht As Worksheet
For Each wkSht In ActiveWorkbook.Worksheets
If Right(wkSht.Name, 7) = "Records" Then
ActiveWindow.SelectedSheets.Visible = True<--Error
End If
Next
End Sub

At the marked line of code, I get "Method visible of object sheets
failed" message. I can restore the sheets by writing a macro that
specifically spells out each sheet name, but since there are over 20
sheets, this would be time consuming.
I also have a backup from early today, but if I use that, I lose all
the work I've done, both on the macros and the worksheets themselves,
since early today.
Does anyone know if it's possible to restore the worksheets without
having to do them individually?
Thanks for the help.
 
H

Harald Staff

Hi

Try

Sub RecShtUnHide()
Dim wkSht As Worksheet
For Each wkSht In ActiveWorkbook.Worksheets
wkSht.Visible = True
Next
End Sub

Note that a hidden sheet can not be active or selected, so that may be the
problem with your code.

You could also open the VB editor (Alt F11 or similar). Select a sheet in
the project explorer and manipulate its Visible property in the Properties
window. (Both found in the View menu if not present already).

HTH. Best wishes Harald
 
S

STEVE BELL

A simple change to your code. Also use UCase to avoid case differences.

Sub RecShtUnHide()
Dim wkSht As Worksheet
For Each wkSht In ActiveWorkbook.Worksheets
If UCase(Right(wkSht.Name, 7))= "RECORDS" Then
wkSht.Visible = True
End If
Next
End Sub
 
V

Vasant Nanavati

Instead of:

ActiveWindow.SelectedSheets.Visible = True

try:

wkSht.Visible = True
 
D

davegb

Thanks to both of you, both solutions worked!
I did find a way around my problem after I posted. I opened the backup,
and copied each sheet name into a macro which had a specific line to
unhide each sheet by name. Cumbersome, but better than typing them all
in, and it worked. But much prefer to have what you wrote in case I
mess up again!
 
S

STEVE BELL

A quick and dirty way to get a list of worksheet names in a workbook.

Add a dummy sheet. Leave the name as "Sheetx"
Leave this sheet the active sheet

Sub ShtList()
Dim wkSht As Worksheet, x as Interger

For x = 1 to ActiveWorkbook.Worksheets.Count
If UCase(Left(Sheets(x).Name,5) <> "Sheet"
Cells(x,1)=Sheets(x).Name
End If
Next
End Sub
 
D

davegb

With a couple of minor corrections, it worked great.
Had to add another parenthesis after .Name,5)
And it didn't like a variable called "interger".
Thanks for the help, I was thinking about doing something like this
next!
 
N

Neil

Dave,

Change
ActiveWindow.SelectedSheets.Visible = True
to
wksht.visible = true


Regards
Neil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top