Saving Last User Name and Date

[mvita]

After spending over 3 hours reading through the threads, I have not found what I am looking for. So if someone finds an answer already mentioned, please show link and accept my appology!

My problem:
I have a workbook which over 100 users can adjust one of the sheets. The sheet is named "M-Data"

Users need a password to access this sheet. So this code works well:
Private Sub Worksheet_Activate()
pword = InputBox("Please Enter a Password to edit the data", "Password Needed to Edit/View M-data Sheet")
If pword <> "123" Then ActiveSheet.Visible = False
End Sub

So sheet hides if wrong password is given.

Now my issue:

If the user gives the correct password, I want the user to give his/her user name and the date s/he changes the M-data.

So I include 2 more InputBox
NameUser = InputBox("Please enter your Name: ") 'example Frank H' (pls. see desert also)
Date = InputBox("Please enter date: ") 'example 22-05-2008'

Now, this is where I am getting stuck. Say 'John M' was the last user who had changed the M-data on 18-04-2008, than I want the following message box to appear showing:

"M-Data was last changed on 18-04-2008 by John M"

Therefore, Frank H will know that 4 weeks ago, John M had changed the M-data. As soon as Frank H makes the changes and saves the workbook, the old data (John M and 18-04-2008) gets replaced by (Frank H and 22-05-2008).

Can someone please help with this code?

Desert:
Can I use a list (also in the M-Data) which will verify if Frank H and John M are "authorized" users who can change the M-Data?

Thanks for reading this "story".....

Anyone please..........

 

[Datsmart]

Take a look at Joe Was' suggestions and code on page two of this thread:
http://www.mrexcel.com/forum/showthread.php?t=194764

This should give you some ideas to automatically get User names and dates rather than asking for them.

 

[mvita]

Datsmart, Thanks for the links. It helped with the "Desert" part of my question. Unfortunately, the 100 odd users are not part of a domain, some are just stand alone PC's in very far away "remote" locations, and thus adding the list of authorized users and veryHiding them will definitely be a plus.

If no UseName has been entered in the "tools-Options-General, User Name" box, (something which I can not influence), the best option for me is to proceed with the Access Sheet.

This all will add extra security to avoid unauthorized use of the M-Data sheet. Thus the enter UserName question remains but will be checked. The Date question can be addressed by taking over the system date.

However, how does one keep the "tracking" of last changed by "Username*1" on "Date" so that I can use this data in the MsgBox?

Thanks a lot so far.

 

[Datsmart]

This thread includes code that records changes made to a workbook:
http://www.mrexcel.com/forum/showthread.php?p=1309105

 

[mvita]

John,
Excellent. Will give it a try and see if the track changes "fits" my needs. From the first looks, it should.

Thanks a lot for your valued replies.

Kind regards.

Akil