capturing date for a change in a row of data

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

james
Fri Jul 25 12:35:47 CDT 2008

On Jul 25, 11:52=A0pm, Mark <M...@discussions.microsoft.com> wrote:
> I want to capture (automatically) the date any change is made in a row of
> data. =A0Capture would be in a cell on the same row. =A0I am not VBA prof=
icient,
> can anyone help?
>
> Thanks

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) =3D 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) =3D 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) =3D 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

Mark
Fri Jul 25 14:48:14 CDT 2008

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

"james.billy@gmail.com" wrote:

> On Jul 25, 11:52 pm, Mark <M...@discussions.microsoft.com> wrote:
> > 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
>
> 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
>