New to Excel VBA: update cell when row changes

[mateo107]

Hello All,

I've done some extensive VBA work in Access, but I now need some VBA help in Excel.

Here's what I'd like to do...
When a workbook is opened, popup asking for a "Enter your First and Last Name", which will be stored as a String >> this I can handle.

What i need help is...
Whenever a row is updated with data, I would like to add the shortdate and ":" and then [Name String] to row "L". I'm trying to add a custom tracking log to see who is updating rows of data in my excel spreadsheet.

Any ideas?
Thanks!

PS: I'm using Excel 2000 / 2003

[EDIT]:
After a little more thought... I'd like to track changes in this manner...
I have columns A-K that I'd like to monitor. If ANY of these fields are updated, THEN write the ShortDate & ":" & [String'd NAME] into the corresponding row on Sheet3 titled "Modifications"

For example:

if 'Jane Smith' updates B1632 on 2/23/06, on the sheet titled "Modifications", i'd like to see: 02/23/06:Jane Smith on row 1623 (In Column A is fine)

if 'John Doe' updates G and K 22 on 2/24/06, on the sheet titled "Modifications, i'd like to see: 02/24/06:John Doe on row 22 (In column A is okay)

Does this make sense? I guess the question is multi-parted.

[Comintern]

You just need to put code in the worksheet's change event. The loops make sure you capture every change if multiple cells are changed at once. MAKE SURE you put in a 'code in progress' indicator if you are changing cell values in this event, or you will create an infinite loop.

Option Explicit
 
Private bChanging As Boolean
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim lRow As Long, lCol As Long
    
    If Not bChanging Then
        For lRow = 1 To Target.Rows.Count
            For lCol = 1 To Target.Columns.Count
                Debug.Print "Cell " & Target.Cells(lRow, lCol).AddressLocal & " was modified."
                bChanging = True
                'Your code here.
            Next lCol
        Next lRow
        bChanging = False
    End If
 
End Sub

[mateo107]

Thanks for a quick response... just a question (sorry, I'm such a n00b right now)...

in the section of "your code here"... do you think you could provide some insight in terms of what code I'd put there...

If i'm not mistaken, thats where I'd put the code to actually put the UName and Date into the worksheet called "Modifications", but I have no base syntax to go off of for this...

Anything more anyone could provide would be MUCH appreciated!

[Comintern]

in the section of "your code here"... do you think you could provide some insight in terms of what code I'd put there...

If i'm not mistaken, thats where I'd put the code to actually put the UName and Date into the worksheet called "Modifications", but I have no base syntax to go off of for this...

Yep. All you need to do is pull the row number out of the cell address, build a new cell address to write to, and write your output. Try this out:

Option Explicit
 
Private bChanging As Boolean
Private sName As String
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim lRow As Long, lCol As Long, sAddress As String
    Dim sWriteTo As String, sRow As String, sOut As String
    
    If Not bChanging Then
        For lRow = 1 To Target.Rows.Count
            For lCol = 1 To Target.Columns.Count
                'Take out the $ markers.
                sAddress = Replace(Target.Cells(lRow, lCol).AddressLocal, "$", vbNullString)
                'Turn on the 'in progress' indicator.
                bChanging = True
                'Get the real row number.
                sRow = GetAbsoluteRow(sAddress)
                'Build the cell address to write to.
                sWriteTo = "A" & sRow
                'Build the output string.
                sOut = Format$(Now, "Short Date") & ":" & sName & " on row " & sRow & "."
                'Write it to the worksheet.
                Target.Parent.Range(sWriteTo).Value = sOut
            Next lCol
        Next lRow
        'Turn off the indicator.
        bChanging = False
    End If
 
End Sub
 
Private Function GetAbsoluteRow(sAddress As String) As String
 
    Dim iCount As Integer, sTest As String, sRow As String
    
    For iCount = 1 To Len(sAddress)             'Loop until you find a number
        sTest = Mid$(sAddress, iCount, 1)       'Test each character.
        If IsNumeric(sTest) Then Exit For       'If it's a number, we're done.
    Next iCount
    
    GetAbsoluteRow = Right$(sAddress, Len(sAddress) - (iCount - 1)) 'Return the number part.
 
End Function