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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi Barrie

Nothing in the Excel Sheet as I cannot see it. If you were looking, you would not know it exisited.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top