Solved: Need Windows Login of User who changed the data

[DonnaDiablo]

Hi Guys...

I've built an Excel spreadsheet (Using Excel 2003 SP3) which i've protected so that not all the columns/rows can be edited. Details of Column Headings are as follows:

Location = A1 (Column Protected)
Screen Text = B1 (Column Protected)
Terminal Inspected = C1 (Column Protected)
Date of Last Check = D1 (Column Not Protected - only field users can change)
Last Check Carried Out by = E1 (Column Protected)

I want the user to be able to enter a date they checked something in Column D ("Date of Last Check") and when this happens, i'd like the next cell on the same row ("Last Check carried out by") to automatically populate with their Username. This cell cannot be edited by the user. The spreadsheet has about 165 rows and when a user accesses the spreadsheet, they may only update a couple of rows. I only want the username to be applied to the same row. Does that make sense?? Several users may access this spreadsheet in a day, so thats why i only want the changes that a particular user makes to have their username logged against it.

The Code I have so far is as follows and this is written in the Target Cell (Column D). I want Column E to populate with the UserName (but ONLY if Column D has changed) so do I need to put an =UserName() in column E??

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(0, 1) = Environ("username")
End If

End Sub

Can anyone point me in the right direction so that I can get this to work? I'm a newbie at VBA, so please be gentle.

Thanx in anticipation of your assistance.

Donna :o)

[p45cal]

Put nothing in the cells on the sheet.
Right click the sheet's tab and choose 'View Code...' where the flashing cursor is paste the following block of code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then '5 is column E
Target.Offset(0, 1) = Environ("username")
End If
End SubClose the newly opened window, go back to the sheet, enter a few things in column D and see what appears in the next column. Do this on an unprotected sheet to start with. If you like it, saving the file will save the macro with it.
You're going to have to add stuff later to counter the things you haven't expected/don't want!

[mdmackillop]

You'll need to add lines to Unprotect/Protect to permit the changes, or otherwise protect using UserInterfaceOnly

[DonnaDiablo]

Hi Guys...

Thank you sooo much for your help. It all works a treat now.

I added in code to the 'This Workbook' module, as follows to allow the UserName to be posted to the protected cell:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="test", _
userinterfaceonly:=True
Next ws
End Sub

I am one very happy bunny.

Thanx again.

Donna ;o)