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.

 

[pedie]

Are you looking for something like this???

try this:

sub inputbox_users()
dim username as string
username = inputbox("Please enter your username")
Range("A" & rows.count).End(xlUp).offset(1).value = username
End sub

 

[pedie]

Or otherwise you may also use the function track changes located under review tab in excel 2007. We can use that to track any changes that a user made in each cell @ what time, but if the different users are logged in to computer using same id then it will show the computer name only....

 

[Blade Hunter]

You can use this to return the logged in windows username:

Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long
Global Const NoError = 0       'The Function call was successful

Sub GetUserName()
   Const lpnLength As Integer = 255
   Dim status As Integer
   Dim lpName, lpUserName As String
   lpUserName = Space$(lpnLength + 1)
   status = WNetGetUser(lpName, lpUserName, lpnLength)
   If status = NoError Then
      lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
   Else
      MsgBox "Unable to get the name."
      End
   End If
   MsgBox lpUserName
End Sub

You can then simply stamp that in your logs and no one needs to log in to the sheet .

 

[sprucethebruce]

I'm only using excel 2003 (not my choice) unfortunately.

The problem is the windows log on will be a generic log on that multiple users will have. I was hoping to create a log on process in excel to narrow it down, hence the need for a username and password for the excel sheet.

As I said, I don't know if this is a very realistic option.

I didn't know about the returning windows username code, that will come in useful for other things though. Thank you.

 

[sprucethebruce]

Are you looking for something like this???

try this:

sub inputbox_users()
dim username as string
username = inputbox("Please enter your username")
Range("A" & rows.count).End(xlUp).offset(1).value = username
End sub

The problem with this is, it requires the user to input their own name, which would require honesty. This may be a good fall back plan if my initial idea isn't possible.

 

[Blade Hunter]

Set up an array in your code with username & "|" & password for each user.

Prompt the user for a username

Store it to a variable

Prompt the user for a password

Store it to a variable

Concatenate your variables with a | in the middle.

Poll through your array looking for the new string, if exist you know who they are, if not tell them they do not have access.

Lock your VBA code to stop snooping eyes on the logins and passwords.

 

[pedie]

The problem with this is, it requires the user to input their own name, which would require honesty. This may be a good fall back plan if my initial idea isn't possible.

Forget the honesty part...
you can assign a username and password to this function...which means that only if the user enters correct information you give to them they will be able to log in.
but this does not track the changes that will be done by this user...


The code by
Blade Hunter
was a very good example we may use here too

 

[Blade Hunter]

The code by
Blade Hunter
was a very good example we may use here too

This is one of my most used bits of code. Every report I write for the business is updateable from the database so people can refresh the data. First thing it does is work out who they are, uses a readonly login to our DB to match them to their DB login then everything they do is as themselves. This way they don't need to be prompted for a login to the DB but they are still restricted by all the DB rights placed upon them, if they try to refresh some data they are not entitled to, it simply tells them they don't have access .

 

[sprucethebruce]

Set up an array in your code with username & "|" & password for each user.

Prompt the user for a username

Store it to a variable

Prompt the user for a password

Store it to a variable

Concatenate your variables with a | in the middle.

Poll through your array looking for the new string, if exist you know who they are, if not tell them they do not have access.

Lock your VBA code to stop snooping eyes on the logins and passwords.

I understand that in patches and can see in theory how that would work. The problem here lies in me compiling that into a workable code.