User name and password to open excel file

mezr · Jun 7, 2003

I know that Excel files can be password protected, but is it possible to create multiple, unique user names and passwords that would allow only a select group of individuals to open an excel file?

Also, to take it even a step further, if the above is possible, then is it possible to cause the file to automatically create a running list of what day and time which user accessed the file?
It seems like a lot to ask, but I'm so often amazed by what I've learned on this board that I figured it was worth a shot!

phantom1975 · Jun 7, 2003

In order for this solution to work, you really should password protect your VB Project. Create a list of user names, passwords, and access to sheets like this:

zzzzzz.xls

A

B

C

D

E

1

UserName

Password

Sheet1

Sheet2

Sheet3

2

Billy

Dumb-butt

x

3

Tom

Moldy

x

4

Juan

Stinky

x

User List

Make this sheet xlSheetVeryHidden for it's Visible property. Create a worksheet called WELCOME SCREEN that will basically be a background or even just an all black page. Then try this code out:PrivateSub Workbook_Open()Dim ShAs WorksheetDim UserNameAsStringDim PasswordAsStringDim ThisCellAs RangeDim cAsLong
 ForEach ShIn ThisWorkbook.Worksheets
 If Sh.Name<> "WELCOME SCREEN"Then
 Sh.Visible = xlSheetVeryHidden
 EndIf
 Next Sh
 UserName = InputBox("Please enter your user name.")
 Password = InputBox("Please enter password.")
 ForEach ThisCellIn Sheets("User List").Range("A2:A" & Sheets("User List").Range("A65536").End(xlUp).Row)
 If UCase(ThisCell.Value) = UCase(UserName) And UCase(ThisCell.Offset(, 1).Value) = UCase(Password)Then
 MsgBox "Access Granted"
 For c = 2To 4
 'This is the number of sheets from C1 to E1
 If ThisCell.Offset(, c).Value<> ""Then
 Sheets(Sheets("User List").Cells(1, c + 1).Value).Visible = xlSheetVisible
 EndIf
 Next c
 ExitSub
 EndIf
 Next ThisCell
 MsgBox "Access Denied"
 ThisWorkbook.CloseEndSub

phantom1975 · Jun 7, 2003

You can then add something like:

Sheets("User List").Range("F" & ThisCell.Row).Value = Now

so that you can have the last date and time that user accessed the file.

pukhovitsky · Oct 20, 2003

could you do the same visibility thing with just a cell not the whole worksheet. if yes please let me know what changes should i make in the code. Thanx and i dont' quiet get it. Shall i create a worksheet just for the users and their password or just type it in straight into the code
thanx again

phantom1975 said:
In order for this solution to work, you really should password protect your VB Project. Create a list of user names, passwords, and access to sheets like this:
zzzzzz.xls
A B C D E
1 UserName Password Sheet1 Sheet2 Sheet3
2 Billy Dumb-butt x x
3 Tom Moldy x x
4 Juan Stinky x x x
User List

Make this sheet xlSheetVeryHidden for it's Visible property. Create a worksheet called WELCOME SCREEN that will basically be a background or even just an all black page. Then try this code out:PrivateSub Workbook_Open()Dim ShAs WorksheetDim UserNameAsStringDim PasswordAsStringDim ThisCellAs RangeDim cAsLong
 ForEach ShIn ThisWorkbook.Worksheets
 If Sh.Name<> "WELCOME SCREEN"Then
 Sh.Visible = xlSheetVeryHidden
 EndIf
 Next Sh
 UserName = InputBox("Please enter your user name.")
 Password = InputBox("Please enter password.")
 ForEach ThisCellIn Sheets("User List").Range("A2:A" & Sheets("User List").Range("A65536").End(xlUp).Row)
 If UCase(ThisCell.Value) = UCase(UserName) And UCase(ThisCell.Offset(, 1).Value) = UCase(Password)Then
 MsgBox "Access Granted"
 For c = 2To 4
 'This is the number of sheets from C1 to E1
 If ThisCell.Offset(, c).Value<> ""Then
 Sheets(Sheets("User List").Cells(1, c + 1).Value).Visible = xlSheetVisible
 EndIf
 Next c
 ExitSub
 EndIf
 Next ThisCell
 MsgBox "Access Denied"
 ThisWorkbook.CloseEndSub

Smitty · Oct 20, 2003

could you do the same visibility thing with just a cell not the whole worksheet. if yes please let me know what changes should i make in the code. Thanx and i dont' quiet get it. Shall i create a worksheet just for the users and their password or just type it in straight into the code

Unfortunately, you can't hide just a single cell, but you do have several options: Hide the Row or Column, set that cell's Format--Protection to Hidden, or set its Font color to White. (Although with that method, if a user highlights a range of cells contiguous to that one, they'll see the cell's contents).

As for the second part, follow Phantom's example. It can be a bit if a pain in the butt to have to modify your code, finding individual user names and passwords.

Hope that helps,

Smitty

Iridium · Oct 20, 2003

or set its Font color to White

Another option for this bit of Smitty's suggestion is a custom format of ";;;" without quotes. Then if you protect the sheet checking the hidden box in the cells format the formula won't be displayed in the formula bar. the custom format works despite what colour formats are set in the cell. Adjust the permutations of cell formats to suit

HT makes sense!

Smitty · Oct 20, 2003

Nice catch! I completly forgot about that one.

Smitty

mikeprice53 · Oct 22, 2003

Thsi works great but I have only 1 question about it which is:

If I have ran this once and got all my workbook set up correctly how do I then get the User list sheet visible again to add more users !

PS I also want to set this to be visible for me ! The ADMIN )

thanks

Smitty · Oct 22, 2003

I take it that you've locked your self out of the worksheet?

Insert a new module in VBA and put:

Code:

Sub UnhideAll()
    Dim ws As Worksheet
    Application.DisplayAlerts = False
        For Each ws In ActiveWorkbook.Worksheets
            ws.Visible = True
        Next ws
    Application.DisplayAlerts = True
End Sub

Then add yourself to the User list or use the following in the ThisWorkbook module

Code:

Private Sub Workbook_Open()
CurrentUserName = Environ("Username")
        If CurrentUserName = "yourusername" Then
            Exit Sub
    End If
End Sub

Hope that helps,

Smitty

mikeprice53 · Oct 23, 2003

Cheers Pannysaver,

Ive been trying to implement the bottom bit now that I have gotten back into the VBA code and cant actually seem to get the Environment Username part to work.

When you state "add this into the thisworkbook module" do you mean object as it wont let me create a module and rename it to that title. Im presuming that you mean the one that is already there with sheet1 sheet2 and sheet3 when you have a brand new workbook ?

secondly how exactly does the environment part work - I am on a works system that works off our surname then our first initial hence "pricem" or does it actually work from a windows login ( which this might well be im not quite sure ...

thanks for your help thus far ...

Mike

User name and password to open excel file

mezr

Active Member

Excel Facts

phantom1975

MrExcel MVP

phantom1975

MrExcel MVP

pukhovitsky

New Member

Smitty

Legend

Iridium

Well-known Member

Smitty

Legend

mikeprice53

Active Member

Smitty

Legend

mikeprice53

Active Member

Similar threads

Forum statistics

Share this page

User name and password to open excel file

Active Member

Excel Facts

MrExcel MVP

MrExcel MVP

New Member

Legend

Well-known Member

Legend

Active Member

Legend

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock