DonnaDiablo
09-18-2009, 02:01 PM
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)
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)