How do I write a macro that will allow users to set thier own pass

D

David A.

I need to let the user chose thier own password. Of course I will need a
master password for my self to reset anyone that forgets theirs. Is there a
way to do this?
 
G

Gord Dibben

No such thing as a Master password that overrides individual passwords

Keep a list of the users' passwords handy so's you can assist when they forget.


Gord Dibben MS Excel MVP
 
G

Gord Dibben

The question "why do you need multiple passwords to open the workbook?" comes to
mind if users are opening just the single workbook.

Back to the question "How do I set up multiple log ins and passwords for this
workbook?"

You could have them first open a dummy workbook with select case code to trap
their login name and ask for a password that matches that name.

If correct password entered, the workbook would open.

There may be another method depending upon why you want this.

Do you want to deny access to some users?

Do you want the workbook to open with just certain sheets visible depending upon
which user opens the workbook?


Gord

How do I set up multible log ins and passwrods for this workbook?
 
D

David A.

I managers that have teams. I use a workbook to track any errors that a
managers team memeber makes. I need to open just those that are assigned to
a particular manager. I don't want managers looking at other managers errors.
So if I set it up so that they creat a log in ( or I assign one) and
password it would accommplish my task.
 
G

Gord Dibben

Note: the following is contingent upon users enabling macros.

If they don't only the "Dummy" sheet will be visible.

I assume you are on a network(LAN) with users logging into the system.

I would set it up so that whichever user's login name is flagged, all sheets
except that user would be hidden.

No password to open the workbook, just code to make a user's sheet visible.

In the Thisworkbook Module....................

Private Sub Workbook_Open()
Dim pword As String
Select Case Environ("Username")
'if a login is not used change to
'pword = InputBox("Enter Your Password")
'Select Case pword
Case Is = "Gord": Sheets("Gordsheet").Visible = True
Sheets("Dummy").Visible = False
Case Is = "Pete": Sheets("Petesheet").Visible = True
Sheets("Dummy").Visible = False
End Select
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

To allow you to see all sheets and edit them.

In a general module...............

Sub UnHideAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Visible = True
Next n
Application.ScreenUpdating = True
End Sub

Naturally you want all this code inviisble to the users.

Right-click on the workbook/project in VBE and select VBAProject Properties and
"Lock project for viewing"

Enter a password.


Gord
 
Top