Username and Passwords on a Worksheet

sprucethebruce

Board Regular
Joined
Jul 22, 2010
Messages
78
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Are you looking for something like this???

try this:
Code:
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
:p
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
You can use this to return the logged in windows username:

Code:
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

Board Regular
Joined
Jul 22, 2010
Messages
78

ADVERTISEMENT

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

Board Regular
Joined
Jul 22, 2010
Messages
78
Are you looking for something like this???

try this:
Code:
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
:p

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

Well-known Member
Joined
Mar 13, 2008
Messages
3,147

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
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

Board Regular
Joined
Jul 22, 2010
Messages
78
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. :(
 

Watch MrExcel Video

Forum statistics

Threads
1,132,823
Messages
5,655,475
Members
418,205
Latest member
jacc99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top