Hide a sheet and Password protect it

[adityad]

I want to hide a sheet and I can do that. However, what I want to do it that when the user wants to view the sheet I just hid, he has to unhide and then enter a password. Right now tthere is no password.

 

[hpdaxelsson]

Exactly right - a before save event will hide all the sheets but your welcome sheet.....

This is a bit over my skill level. VBA and I don't match....

However I found a macro that will force the user to enable macro:
Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If

'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

And then I have the other macro that will hide the specfic sheets that I would likt to hide:

Private Sub Worksheet_Activate()
pword = InputBox("Please Enter a Password", "Unhide Sheets")
If pword <> "1" Then ActiveSheet.Visible = False
End Sub

My big issue is how do I get them to work togther???????

/H

 

[gstullo]

Removed post

 

[gstullo]

I am revisiting my workbook after two years of using the "very hidden" option...

As our staff have moved to Excel 2007 and 2010 we run into the problem of people saving the workbook without re-hiding the sheets.

When someone opens the file again, the hidden sheets are shown until the person enables macros... then the sheets hide again.

Any workaround?

-GST-

 

[wilcowoods]

If you have saved your file once with all sheets very hidden (except a "welcome" page), and assuming you have a before save event handler running, the users will have to enable macros before using the sheet, and if they save the sheet the before save should hide all sheets except the welcome page. Pretty bullet-proof, I reckon

 

[gstullo]

I agree that it would be bullet proof however it relies on the user to hide the sheets before saving. There is no way to guarantee that they do this! Too much risk!

 

[SuperFerret]

gstullo, it doesn't rely on the user to save the sheets before hiding.
This is handled in the Before Save Macro, which would hide all the sheets before actioning the save.

We use this on a large spreadsheet in my office and haven't had any issues with it.

 

[Microsoft Office Support]

Yes, you can do it, without using Macro, by taking advantage of the Excell 2010 Protect Workbook feature by doing the following...

Step 1.
Right-click on the worksheet that you want to hide and protect and click Hide.

Step 2.
Go to Review > click Protect Workbook and enter a password, afterwards, make sure the option of Structure is checked and click OK.

Save and close the Excel 2010, then wola! When you re-open the workbook, the sheet that you have hidden isn't there until user click Unprotect Workbook and enter the correct password.

It's that simple.

 

[Excel777]

Yes 'Microsoft Office Support' it's a practical and easy Method

 

[Microsoft Office Support]

This posted solution is sponsored by official Microsoft Office support group.

We are glad we can rsolve this issue.

 

[keifallen]

ITS EASIER THAN WRITING CODE!

Simple steps for novices.

1) Right click on the tab at the bottom of the page and left click 'view code'

2) When in Microsoft Visual Basic select the sheet you want to hide and in the properties box click on the drop down menu which will show you 'visible', 'hidden' and 'very hidden'

3) Click on 'very hidden' and repeat for any other worksheets or charts you want to hide

4) Right click on any sheet in the VBAproject window and select 'VBAProject Properties'

5) Click on 'protection' and then you tick the box for password and write in a password

6) Close the MVBA and you'll see the sheets are hidden and don't appear when you go to 'format, sheet, unhide'