Username and Passwords on a Worksheet

[sprucethebruce]

I don't really know where to start with this problem.

I want to be able to track who is entering data into a spreadsheet. Ideally everyone (around 50 people) has their own username and password and they have to enter this to access the spreadsheet and once they access it, there name is stored along with date and time.

Is there anyway to attach a username and password function to excel and then to make it work ina manner I describe.

Any help would be greatly appreciated.

 

[Blade Hunter]

Put this at the workbook level:

Private Sub Workbook_Open()
Call CheckUser
End Sub

Put this in a module:

Sub CheckUser()
Dim UserPass As Variant
Dim LoginAllowed As Boolean
Dim UserName, PassWrd As String
UserPass = Array("Dan|Pass", "Bob|Hello", "Janice|World")
UserName = InputBox("Enter Username", "Login")
PassWrd = InputBox("Enter Password", "Login")
LoginAllowed = False
For X = LBound(UserPass) To UBound(UserPass)
    If UCase(UserPass(X)) = UCase(UserName) & "|" & UCase(PassWrd) Then LoginAllowed = True
Next
If LoginAllowed Then
    MsgBox "Login Succesful"
Else
    MsgBox "You dont have access"
    'Actworkbook.Close False 'Commented out for testing
End If
End Sub

That should be enough to get you started .

Cheers

Dan

 

[pedie]

That was great example...but even if you enter incorrect username and pass it will allow you to access and make changes even though it may say "you dont have access" msg...
so you better to a close workbook function there so that when incorrect info is entered the workbook closed .....just suggestion...

 

[lenze]

You could use a cell comment to track changes. See my VBAExpress article Tracking Changes With Comments
You could even add a line to require a password if you really need that!

lenze

 

[sprucethebruce]

Folks,

Thanks very much for your input.

Will give that code a try Dan.

 

[Blade Hunter]

That was great example...but even if you enter incorrect username and pass it will allow you to access and make changes even though it may say "you dont have access" msg...
so you better to a close workbook function there so that when incorrect info is entered the workbook closed .....just suggestion...

Look at the 3rd last line in the code

Edit: Whoops, typo:

'Actworkbook

Should be

'Activeworkbook

 

[sprucethebruce]

Very very good.

I've uncommented the section for closing the workbook but keep getting a runtime 424 error, object required on the Actworkbook.Close False line. I'm sure its something basic I'm missing. Doh.

Else
MsgBox "You dont have access"
Actworkbook.Close False
End If

 

[Blade Hunter]

Very very good.

I've uncommented the section for closing the workbook but keep getting a runtime 424 error, object required on the Actworkbook.Close False line. I'm sure its something basic I'm missing. Doh.

Yeah it was a typo, make it Activeworkbook.close false

 

[Blade Hunter]

How secure do you want it? We can also disable the esc key if you need so the user can't interupt the code. They will always be able to choose to open without macros though. There are ways around this with hidden sheets and password protection.

 

[sprucethebruce]

Yeah, that was my next question about the opening by disabling macros. If it isn't an option to skip this question and enable macros automatically, I'd just create a button required to be pressed to make the sheet visible or something along those lines, so opening without enabling macros would give you nothing but a blank sheet.

How exactly does the escape key interrupt the code?

 

[Blade Hunter]

Yeah, that was my next question about the opening by disabling macros. If it isn't an option to skip this question and enable macros automatically, I'd just create a button required to be pressed to make the sheet visible or something along those lines, so opening without enabling macros would give you nothing but a blank sheet.

How exactly does the escape key interrupt the code?

The hidden sheet method is exactly how to do it but make it part of the workbook open macro, then you don't need a button, workbook opens, they enter their login, it unhides.

If they open and interrupt the code they see a blank sheet, if they open no macros, they see a blank sheet.

Make sure you password protect your module.

The escape key (or interrupt key) can be disabled like so:

Application.EnableCancelKey = xlDisabled

Turn it back on at the end with:

Application.EnableCancelKey = xlInterrupt

Cheers

Dan