Unhide hidden/protected worksheets
 

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.

Unhide hidden/protected worksheets
 

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


"davegb" skrev i melding
ups.com...
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.

Unhide hidden/protected worksheets
 

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

--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
ups.com...
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.

Unhide hidden/protected worksheets
 

Instead of:

ActiveWindow.SelectedSheets.Visible = True

try:

wkSht.Visible = True

--

Vasant


"davegb" wrote in message
ups.com...
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.

Unhide hidden/protected worksheets
 

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!


STEVE BELL wrote:
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

--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
ups.com...
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.

Unhide hidden/protected worksheets
 

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



--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
oups.com...

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!


STEVE BELL wrote:
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

--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
ups.com...
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.

Unhide hidden/protected worksheets
 

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!

STEVE BELL wrote:
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



--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
oups.com...

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!


STEVE BELL wrote:
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

--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
ups.com...
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.

Unhide hidden/protected worksheets
 

Dave,

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


Regards
Neil

"davegb" wrote in message
ups.com...
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.