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.

 

[sprucethebruce]

Great stuff, thanks for your help.

 

[Blade Hunter]

No worries, happy to help

 

[sprucethebruce]

I've actually just thought of one more thing which I don't think will be too much trouble for you.

For me, yes, lots of trouble but hopefully it'll be a quick line of code for you.

Once someone logs on, is there a way of there name being placed in a cell (A1 at first then once thats filled, the next cell underneath and so on)and then not being able to be deleted, if the deleted bit is a problem I can just put it in a cell with white text and they will never know they are being logged.

 

[Blade Hunter]

I did something very similar a while back, I had a sheet called Smart Match Log which I populated with the data then locked at the end:

If Sheets("DataMonth").Range("A" & Y).Text = SMListVar Then
    Sheets("Smart Match Log").Range("A" & LogNum).Formula = "Artist: " & Sheets("DataMonth").Range("A" & Y).Formula
    Sheets("Smart Match Log").Range("B" & LogNum).Formula = SmartReplacement
    Sheets("Smart Match Log").Range("C" & LogNum).Formula = Sheets("DataMonth").Range("E" & Y).Formula
    Sheets("Smart Match Log").Range("D" & LogNum).Formula = CurrentUser
    Sheets("Smart Match Log").Range("E" & LogNum).Formula = DatabaseUserName
    Sheets("Smart Match Log").Range("F" & LogNum).Formula = Date
    Sheets("Smart Match Log").Range("G" & LogNum).Formula = Time
    LogNum = LogNum + 1
End If

I used the above and looped through my changes (Lognum increasing each time) this way I not only logged who they are but what they did and more importantly, when they did it .

At the end I outputted the sheets to a new file (I am a big fan of storing sheets off as text, formulas removed, almost a snapshot of data really but it keeps them small if there are no formulas)

Once that was finished the sheet was locked and password protected with:

ActiveSheet.Protect "S8M3_kEy"

Hope that helps in some way

Dan