Hide sheet from view with password

[nparsons75]

Hi,

I have an excel file containing a number of sheets, I will name them sheet 1,2,3,4 and 5.

The issue I have have is this.

Sheet 1 and 2 need to be restricted access. Only certain people can view / edit as they contain sensitive data. Sheets 3,4 and 5 are open access and can be viewed by anyone.

Is there a way of password protecting or another method so that that only certain people can access sheet 1 and 2.

Currently to do this I am having to have two independent workbooks (restricted and open access).

Thanks

 

[Scott T]

In the ThisWorkbook module put the code below. This will hide and protect sheets 1 and 2 when the file is closed and when the file is open it will ask for a password to unhide and unprotect the sheets.

Private Sub Workbook_Open()
pword = InputBox("enter password")
If pword = "password" Then
    Sheets("Sheet1").Visible = True
    Sheets("Sheet2").Visible = True
    Sheets("Sheet1").Unprotect ("passwordhere")
    Sheets("Sheet2").Unprotect ("passwordhere")
Else
    MsgBox ("Incorect password")
End If
 
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Visible = xlSheetVeryHidden
Sheets("Sheet2").Visible = xlSheetVeryHidden
Sheets("Sheet1").Protect Password:="passwordhere"
Sheets("Sheet2").Protect Password:="passwordhere"
ActiveWorkbook.Save
End Sub

 

[nparsons75]

Excellent, i will give that a go and feedback. Will this be easy to crack (get into) if someone knows how to access the VBA code or is there a way to make that harder for them also.

Thanks for your help.

 

[Scott T]

If someone opens the code they can see the passwords.

You can password protect the project, see link, but it is not super secure.
http://www.ozgrid.com/VBA/protect-vba-code.htm

 

[nparsons75]

Hi there,

I just tried the code but I get an abiguous name error due to there being another workbook open command in this workbook (I think that this why I get the error). Is there a way around that would you know? Thank you.

 

[Joe4]

I just tried the code but I get an abiguous name error due to there being another workbook open command in this workbook (I think that this why I get the error).

You cannot have two procedures in the same module with the same name.
You will need to combine them into a single Workbook_Open procedure.

 

[nparsons75]

Hi, since I run this macro, I am getting errors each time I open the file. The password box opens but once entered I get various errors.

Sorry we couldn't find "\\file......

Microsoft excel cannot access the file .......

The filename does not exist...

Cant open pivot table source...

I click through them and the file opens? Any ideas?

 

[Scott T]

could you post the code you are running. The code I posted does nothing with other files or pivot tables so I have no idea what could be the problem.

 

[nparsons75]

Yes, I looked at the code, it does not do anything that should effect things. I think its more a coincidence but it only happens with this file.

This is the code modified to suit.

Private Sub Workbook_BeforeClose(Cancel As Boolean)Sheets("strategic objectives (1)").Visible = xlSheetVeryHidden
'Sheets("Sheet2").Visible = xlSheetVeryHidden
Sheets("strategic objectives (1)").Protect Password:="private"
'Sheets("Sheet2").Protect Password:="passwordhere"
ActiveWorkbook.Save
End Sub

 

[Scott T]

What is your worksheet open code? It could be related to the sheets being protected and it is not unprotected before the other code is run.