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.

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" <[email protected]> skrev i melding
news:[email protected]...
> 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.
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

Instead of:

ActiveWindow.SelectedSheets.Visible = True

try:

wkSht.Visible = True

--

Vasant


"davegb" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.
> >> >
> >

Dave,

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


Regards
Neil

"davegb" <[email protected]> wrote in message
news:[email protected]...
> 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.
>