Prospero
02-14-2012, 04:18 AM
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
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