Several Password

[Bakar]

Hi Everyone
Can someone help I have many users using one workbook
I have to give access to each one with individual password how can
write
a macro so that if one user open he put his name and his password sam
for the second,third etc.....

PS
One workbook is fixed on one computer ( a macro for this)
One workbook is a shared workbook (a macro for this)

Thanks as usual and appreciate ur help

Baka

 

[Ben McClave]

Bakar,

This might work, although you'll want to password protect the code for the userform before circulating your final workbook. The idea of this series of macros is that all but one sheet are "veryhidden" when the file closes. The one visible sheet will have a message that alerts users to ensure macros are enabled, and may also include a button for unlocking the workbook (the code of which would simply be Userform1.Show).

Once you have this first sheet set up, go to the "ThisWorkbook" module and paste the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.CodeName = "Sheet1" Then
ws.Visible = True
Else
ws.Visible = xlSheetVeryHidden
End If
Next ws
End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Then, create a Userform with two textboxes (username and password) and two buttons (OK and Cancel). On the password textbox, set the PasswordChar Property to "*" or another masking character if you wish. Then, in the Userform module, paste this code:

Private Sub CommandButton1_Click()
Dim i As Long
Dim ws As Worksheet
Dim sPWord(1 To 5) As String
Dim sUser(1 To 5) As String

sUser(1) = "Test"
sPWord(1) = "Testme"
'fill in others as well...

Select Case TextBox1
Case sUser(1)
i = 1
Case sUser(2)
i = 2
Case sUser(3)
i = 3
Case sUser(4)
i = 4
Case sUser(5)
i = 5
Case Else
MsgBox "Invalid User ID or Password"
Exit Sub
End Select

If TextBox2 = sPWord(i) Then
For Each ws In Worksheets
If ws.CodeName = "Sheet1" Then
'Do nothing
Else
ws.Visible = True
End If
Next ws
Sheet1.Visible = xlSheetVeryHidden
Unload Me
Else
MsgBox "Invalid User ID or Password"

End If

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub



The first few variables will be the usernames and passwords. If the textbox info matches a username and its corresponding password, all sheets will be made visible except for Sheet1, which will become "VeryHidden". If the entries do not match, the users will receive an error message.

You can customize this code from here, but I think this will get you started.

Ben