Securing a sheet with VBA in excel

[Prospero]

Dear all,

I am creating a new project with a very tight deadline and I am having trouble with some crucial details. I need to secure some of the sheets in my workbook with a password so that they can be accessed only by certain people with a specific password. The data also must stay hidden unless the correct password is given. I searched the internet and found an VBA code that is fitting my needs. There is a problem however. As I am an beginner with VBA, I am not able to modify the code so that it is fully working. Everything works fine, but the main problem is that the data are shown even if an uncorrect password is inserted. I don't have a clue how to fix this. Not being able to secure some of the lists in the way described above will mean a big problem for me. Could somebody please help me? Every suggestion is much appreciated!

My modified code is:
I need to secure "Sheet2" which is named e. g. "Effectifs" with the password "Heslo123" and the user has 3 attemps


Dim correct_pass_given As Integer
Dim hide_sheet As Worksheet


Private Sub Workbook_Open()
correct_pass_given = Heslo123
'Set hide_sheet = Sheet2 '
Sheet1.Select
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim strPass As String
Dim lCount, number_of_tries_allowed As Long
Set hide_sheet = Sheet2 '
number_of_tries_allowed = 3 '
'MsgBox correct_pass_given

If ActiveSheet.Name <> "Effectifs" Or correct_pass_given = 1 Then
Else
hide_sheet.Columns.Hidden = True
'Allow 3 attempts at password
For lCount = 1 To number_of_tries_allowed
strPass = InputBox(Prompt:="Password Please", Title:="PASSWORD REQUIRED")
If strPass = vbNullString Then 'Cancelled
MsgBox "Password incorrect", vbCritical, "Message"
Else: correct_pass_given = 1 'Correct Password
Exit For
End If
Next lCount
If lCount = number_of_tries_allowed + 1 Then '
Exit Sub
Else 'Allow viewing
hide_sheet.Columns.Hidden = False
End If
End If
End Sub

[Bob Phillips]

If you want to hide the data, hide the worksheet.

[Prospero]

Hiding the sheet is not an option, I need it to be accessible for the users. E. g. for HR will be Sheet1 available after inserting a password, for Finance department Sheet2, for PR sheet 3 etc.

[mikerickson]

This code assumes that there is a worksheet Main, that everybody is allowed to see. It requires that BobsPassword be entered to view the worksheet BobSheet. Similarly, MarysPassword is requried to see MarySheet.


[VBA]' in ThisWorkbook code module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim RequiredPassword As String, desiredSheet As Worksheet
Set desiredSheet = Sh

Select Case LCase(Sh.Name)
Case "bobsheet"
RequiredPassword = "BobsPassword"
Case "marysheet"
RequiredPassword = "MarysPassword"
Case Else
RequiredPassword = vbNullString
End Select

If RequiredPassword <> vbNullString Then
On Error GoTo ErrorOut
Application.EnableEvents = False
Sheets("Main").Activate
If Application.InputBox("Enter the password", Default:=RequiredPassword, Type:=2) = RequiredPassword Then
desiredSheet.Activate
Else
MsgBox "Wrong Password"
End If
End If

ErrorOut:
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Main").Activate
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Main").Activate
End Sub[/VBA]

[Bob Phillips]

Hiding the sheet is not an option, I need it to be accessible for the users. E. g. for HR will be Sheet1 available after inserting a password, for Finance department Sheet2, for PR sheet 3 etc.

So hide them all and have a navigation sheet where the user inputs a password
or a form, and unhide the appropriate sheet.

[Prospero]

To mikerickson: Thank you so much! This works like a charm! Absolutely perfect.

xld: I would like to thank you also very much, I will definitely try out what you are suggesting!

BR,

Petr.

[Prospero]

This code assumes that there is a worksheet Main, that everybody is allowed to see. It requires that BobsPassword be entered to view the worksheet BobSheet. Similarly, MarysPassword is requried to see MarySheet.


[vba]' in ThisWorkbook code module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim RequiredPassword As String, desiredSheet As Worksheet
Set desiredSheet = Sh

Select Case LCase(Sh.Name)
Case "bobsheet"
RequiredPassword = "BobsPassword"
Case "marysheet"
RequiredPassword = "MarysPassword"
Case Else
RequiredPassword = vbNullString
End Select

If RequiredPassword <> vbNullString Then
On Error GoTo ErrorOut
Application.EnableEvents = False
Sheets("Main").Activate
If Application.InputBox("Enter the password", Default:=RequiredPassword, Type:=2) = RequiredPassword Then
desiredSheet.Activate
Else
MsgBox "Wrong Password"
End If
End If

ErrorOut:
Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Main").Activate
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Main").Activate
End Sub[/vba]

There is one problem with the solution you suggested - when I go e. g. to the sheet "BobSheet" and I click again on "BobSheet" than I am able to view all the data in BobSheet without actually entering the password. Is there any way arround this e. g. by a command to hide the colums before the password is inserted?

[mikerickson]

I don't understand the problem.

The entirety of BobsSheet should be not viewable except with entering Bobs passoword.

Are you trying to hide portions of a worksheet from not-Bob, but let not-Bob view the other portions?