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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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
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
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. :(
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,060
Messages
5,509,067
Members
408,704
Latest member
Mickael_1973

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top