Security/Viewing access

[DragonPrinces]

Hi guys.
Is it possible to set security levels of access to different sheets or even cells to not be viewable without a password entry?

Basically I have a standard Personnel files containing categories such as.
Personal Information
Banking information
position information
performance review information
Disciplinary information
Salary information

Is it possible to assign Security levels to this information that allows the cells to be viewable only to people with the appropriate passwords?

If this is not possible i was hoping to separate these into different sheets for each category and then protect each sheet with a password. Is that also possible?

 

[Snakehips]

DragonPrinces,

Sadly, you have no sensible option but to separate into different sheets for each category.

Then you will need to do something similar to the below.
If there is one of your sheets that can remain visible to all then use that instead of the "Home" sheet. Otherwise insert a sheet and name it Home. That sheet can be blank.
The code needs to be copied to the 'ThisWorkbook' code module.
Sheet names and passwords would need editing to suit.


Prevent users from saving the file using Save As. You may Save As using password (PW999)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
Login = InputBox("Please Enter Your Password")
If Not Login = "PW999" Then
Response = MsgBox("Sorry, Save As denied.", vbOKOnly, "Login Failed")
Cancel = True
End If
End Sub

When workbook is closed, make all sheets bar the Home sheet xlVeryHidden. Those sheets cannot then be made visible by the user, only by code.
Force save the workbook, with sheets hidden.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Sheets("Home").Visible = True
For Each Sht In ThisWorkbook.Sheets
If Not Sht.Name = "Home" Then Sht.Visible = xlVeryHidden
Next Sht
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub

When workbook is opened, only the Home sheet is visible and a pasword is requested. PW is tested and will unhide sheets accordingly or quit if password is bad.

Private Sub Workbook_Open()
Login = InputBox("Please Enter Your Password")
Application.EnableEvents = False
Select Case Login
Case "PW1", "PW2"
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Case "PW3"
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Case "PW4"
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Case Else
Response = MsgBox("Sorry, access denied.", vbOKOnly, "Login Failed")
ThisWorkbook.Close
End Select
Application.EnableEvents = True
End Sub

You would be as well to password protect the code in the VB editor.

It is not 100% secure. Nothing in Excel is!! But it or something similar, may well suffice. It very much depends upon how knowledgeable your users are and how intent they might be to bypass your efforts to secure.

Hope that helps.

 

[DragonPrinces]

Snakehips

Thanks. I can totally do it this way. The people that will have access to these files are not very Techy so the security level is perfect.

I have never used any of the VB coding or done any coding at all so i am struggling a bit.
I made a band new workbook to test out on. "Security Test" and i have 5 Sheets "Sheet1-5"

Trying out this first code to prevent to save and it gives me an error " Compile Error. Block If without End If."

Then the debugger highlights the very first line in yellow.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Not really sure what i have done wrong.
Sorry about the inexperience.

DP

 

[Snakehips]

DP,

My apologies, carelessness on my part!!!!

That first code should have another End IF!!!!

So last three lines....

End If
End If
End Sub

Give that a try.
If you need further help, that's fine but I will be out shortly and not back until this evening.

 

[DragonPrinces]

Snakehips (Snakes don't have hips? :P)

They all work like a freaking charm man you rock