capturing date for a change in a row of data

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I want to capture (automatically) the date any change is made in a row of
data. Capture would be in a cell on the same row. I am not VBA proficient,
can anyone help?

Thanks
 
I want to capture (automatically) the date any change is made in a row of
data.  Capture would be in a cell on the same row.  I am not VBA proficient,
can anyone help?

Thanks
Click to expand...

You would need to enter a worksheet change event, so go to
tools>macro>visual basic editor. On the left hand side you should see
the workbook (If you don't go to View>Project explorer, from there
expand your current workbook, you should see the different worksheets
that apply to your workbook. Double click the worksheet you want this
to apply to, at the top of the code you have some dropdowns, select
worksheet in the first one and then select Change from the second one.
What you are saying is I want my code to run everytime there is a
change to the worksheet. The following should appear: Private Sub
Worksheet_Change(ByVal Target As Range) Target refers to the cell that
has changed. What you need to decide is which colum the date should
appear...

For example between the Private Sub... and End Sub... you would put:

Cells(Target.row, 5) = Date .... This would mean that in column E of
the row that has changed (E being the fifth column) the date would be
entered.

Something like...

Private Sub Worksheet_Change(ByVal Target As Range)
Cells(Target.Row, 5) = Date
End Sub

Cells is a standard notation for referencing the cell, otherwise you
would use the Range notation, in which case...

Private Sub Worksheet_Change(ByVal Target As Range)
Range("E" & Target.row) = date
End Sub

Again changing "E" to the colum you want to enter the date into, this
would do exactly the same thing, Date is a built in function in VBA.

James
 
Thanks, that worked really well. However....I probably over simplified it.
I want a date only if there is a change, now even a click in a cell does the
data addition. Is there anything I can do to check for an actual change or
addition?

thanks again,

Mark
 
Back
Top