Hidden Sheet - By Code - Excel Gurus Required

ihmunro

Board Regular
Joined
Oct 11, 2002
Messages
74
Afternoon

I have a file that I did not create. The person that did create the file has left the company, so I am stuck with trying to unhide it.

In the Properties windows under Visible it state 0 - xlsheetHidden. When I try to change it to -1 - xlsheetVisible, I get an error saying it cannot be changed.

Any ideas to help me unlock this would be appreciated.

Happy New Year to everyone.

Iain
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
What happens if you try to unhide the sheet from the main menu in Excel (not VBA)? Format|Sheet|Unhide.
 

ihmunro

Board Regular
Joined
Oct 11, 2002
Messages
74
Hi Barrie

Nothing in the Excel Sheet as I cannot see it. If you were looking, you would not know it exisited.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello Iain,
A sheet that is xlVeryHidden can only be un-hidded with code.
If you know the sheet's name then go into the VB Editor, and in a standard module, paste this: (obviously you'll need to replace the text: Enter the hidden sheet's name here with the real name of the hidden sheet...)
Code:
Sub UnHideThatDangSheet()
Sheets("Enter the hidden sheet's name here").Visible = True
End Sub
Then simply run the code.

That should get you going.
 

ihmunro

Board Regular
Joined
Oct 11, 2002
Messages
74

ADVERTISEMENT

Hi HalfAce

Thanks for the code.

After adding in the code and the sheet name, I still get the same error which is "Unable to set the Visible Property of the Worksheet Class"

Iain
 

ihmunro

Board Regular
Joined
Oct 11, 2002
Messages
74
Hi HalfAce

Here is the code I pulled from Module 1

Sub auto_open(Optional fakearg As Integer)

Application.ScreenUpdating = False

On Error Resume Next

SheetCount = ActiveWorkbook.Sheets.Count

rovalue = ActiveWorkbook.ReadOnly



For i = 1 To SheetCount

Worksheets(i).Visible = True

Next i

If rovalue = "True" Then

For i = 1 To SheetCount

Worksheets(i).Select

ActiveWorkbook.ActiveSheet.Unprotect Password:="SMITHTM"

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Locked = True
Selection.FormulaHidden = True

ActiveWorkbook.ActiveSheet.Protect Password:="SMITHTM"

Next i


End If

ActiveWorkbook.Sheets("Display").Visible = False
ActiveWorkbook.Sheets("CC").Visible = False
Worksheets(1).Select
Range("A2").Select

ActiveWorkbook.Protect Password:="SMITHTM"
Application.ScreenUpdating = True

End Sub

Sub auto_close(Optional fakearg As Integer)

Dim Msg As String
If ActiveWorkbook.Saved = True Then
Call HideSheets
Exit Sub
Else

Msg = "Do you want to save the changes you made to "
Msg = Msg & ActiveWorkbook.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
ActiveWorkbook.Save
Call HideSheets
Exit Sub
Case vbNo
ActiveWorkbook.Saved = True
'Call HideSheets
Exit Sub
Case vbCancel
cancel = True
End Select

End If

End Sub

Sub HideSheets(Optional fakearg As Integer)

Application.ScreenUpdating = False

Application.DisplayAlerts = False
On Error Resume Next

ActiveWorkbook.Sheets("Display").Visible = True

SheetCount = ActiveWorkbook.Sheets.Count

For i = 1 To SheetCount


Worksheets(i).Select

If ActiveWorkbook.Sheets(i).Name <> "Display" Then

ActiveWorkbook.Sheets(i).Visible = False

End If

Next i
Application.EnableEvents = False
ActiveWorkbook.Save
Application.Quit

End Sub
 

rogerbh

New Member
Joined
Dec 23, 2005
Messages
8

ADVERTISEMENT

Not sure but try copying and pasting the code below into a new module into the file and running it. Let me know if it works. Good luck!
---------------------------------------------------------------------------------

Sub UnHide_All()
Dim sheet As Worksheet

For Each sheet In ActiveWorkbook.Worksheets
If sheet.Name <> ActiveSheet.Name Then

sheet.Unprotect Password:="SMITHTM"
sheet.Visible = xlSheetVisible

End If

Next sheet


End Sub
 

ihmunro

Board Regular
Joined
Oct 11, 2002
Messages
74
Hi RogerBh

THanks for the code. I thought it was working until I got the following error:

Run Time Error 1004

Method 'Visible' of object '_worksheet' failed

I hope you can help.

Iain
 

rogerbh

New Member
Joined
Dec 23, 2005
Messages
8
I made a slight modification to the first piece of the code "auto_open" that you had sent earlier. Copy this into module 1 and run it. Sheets "Display" and "CC" should be visible.

-------------------------------------------------------------------------------------

Sub auto_open()

Application.ScreenUpdating = False

On Error Resume Next

SheetCount = ActiveWorkbook.Sheets.Count

rovalue = ActiveWorkbook.ReadOnly



For i = 1 To SheetCount

Worksheets(i).Visible = True

Next i

If rovalue = "True" Then

For i = 1 To SheetCount

Worksheets(i).Select

ActiveWorkbook.ActiveSheet.Unprotect Password:="SMITHTM"

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Locked = True
Selection.FormulaHidden = True

ActiveWorkbook.ActiveSheet.Protect Password:="SMITHTM"

Next i


End If

ActiveWorkbook.Sheets("Display").Visible = True
ActiveWorkbook.Sheets("CC").Visible = True
Worksheets(1).Select
Range("A2").Select

ActiveWorkbook.Protect Password:="SMITHTM"
Application.ScreenUpdating = True

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,086
Messages
5,570,136
Members
412,305
Latest member
Mozz
Top