CODEWITHSUNDEEP
excelvba
JBeets
JBeets

Reputation: 23

Excel VBA – Insert Username when cell is changed

I found some code on this site that I think will solve my issue however when I add it to my workbook and make a change to a cell in the specified column nothing happens. I would like to see if anyone would be able to tell me where I went wrong. What I need it to do is to add the UserName and a TimeStamp in the corresponding cells when a cell in changed in column X (Reviewed by CAT Project). I have put this code into my personal workbook but I dont know if that would be an issue? Also I will need it to work on any worksheet without specifying the name. Here is the code I am using:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ThisRow As Long ' make sure to declare all the variables and appropiate types
ThisRow = Target.Row

'protect Header row from any changes
If (ThisRow = 2) Then

       Application.EnableEvents = False
       Application.Undo
       Application.EnableEvents = True
       MsgBox "Header Row is Protected."
       Exit Sub

End If


If Cells(2, Target.Column) = "Reviewed by CAT Project" Then

    Dim sOld As String, sNew As String
    sNew = Target.Value 'capture new value

    With Application
        .EnableEvents = False
        .Undo
    End With

    sOld = Target.Value 'capture old value
    Target.Value = sNew 'reset new value

    If sOld <> sNew Then

        ' time stamp corresponding to cell's last update
        Range("Z" & ThisRow).Value = Now
        ' Windows level UserName | Application level UserName
        Range("Y" & ThisRow).Value = Environ("username")
        Range("Y:Z").EntireColumn.AutoFit

    End If

    Application.EnableEvents = True

End If

End Sub

Upvotes: 0

Views: 2871

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

Your posted code works if:

  1. Row 2 is setup before the code is entered.
  2. The code is placed in the worksheet code area

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Upvotes: 1

Related Questions