Update date when changing a cell

Hi,

I have a worksheet in excel with a lot of data on it. The data exists of text, numbers and formulas.
I also have a date which should be updated to todays date when any of the cell on the worksheet is changed.
Is it possible to do this and how should the code look like?
Thanks in advance for helping me!

Hi Leo,

Paste the following code in your worksheet's code (right-click on tab,
select View code)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Date
Application.EnableEvents = True
End Sub


HTH
--
AP

"leonidas" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi,
>
> I have a worksheet in excel with a lot of data on it. The data exists
> of text, numbers and formulas.
> I also have a date which should be updated to todays date when any of
> the cell on the worksheet is changed.
> Is it possible to do this and how should the code look like?
> Thanks in advance for helping me!
>
>
> --
> leonidas
> ------------------------------------------------------------------------
> leonidas's Profile:
> http://www.excelforum.com/member.php...o&userid=35375
> View this thread: http://www.excelforum.com/showthread...hreadid=561410
>

Put this behind the worksheet code of the sheet you want it on.
(and change "A1" to the cell you want the date in)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then Exit Sub
Range("A1").Value = Now()
Range("A1").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub


Duncan


leonidas wrote:

> Hi,
>
> I have a worksheet in excel with a lot of data on it. The data exists
> of text, numbers and formulas.
> I also have a date which should be updated to todays date when any of
> the cell on the worksheet is changed.
> Is it possible to do this and how should the code look like?
> Thanks in advance for helping me!
>
>
> --
> leonidas
> ------------------------------------------------------------------------
> leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
> View this thread: http://www.excelforum.com/showthread...hreadid=561410

Leo,

Forget my first post, this is a little better on the eyes. I typed too
fast last time and didnt set the target back to the cell you typed into
originally (which would be annoying when trying to input) and also
forgot to remove the cutcopymode.

Paste this in instead

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OLDT As Range
If Target =3D Range("A1") Then Exit Sub
Set OLDT =3D Target
Range("A1").Value =3D Now()
Range("A1").Copy
Range("A1").PasteSpecial Paste:=3DxlPasteValues, Operation:=3DxlNone,
SkipBlanks _
:=3DFalse, Transpose:=3DFalse
Application.CutCopyMode =3D False
OLDT.Select
End Sub


btw: Ardus: wouldnt your sub loop indefinately?

Duncan


Ardus Petus wrote:

> Hi Leo,
>
> Paste the following code in your worksheet's code (right-click on tab,
> select View code)
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.EnableEvents =3D False
> Range("A1").Value =3D Date
> Application.EnableEvents =3D True
> End Sub
>
>
> HTH
> --
> AP
>
> "leonidas" <[email protected]> a =E9c=
rit
> dans le message de news:
> [email protected]...
> >
> > Hi,
> >
> > I have a worksheet in excel with a lot of data on it. The data exists
> > of text, numbers and formulas.
> > I also have a date which should be updated to todays date when any of
> > the cell on the worksheet is changed.
> > Is it possible to do this and how should the code look like?
> > Thanks in advance for helping me!
> >
> >
> > --
> > leonidas
> > ------------------------------------------------------------------------
> > leonidas's Profile:
> > http://www.excelforum.com/member.php...userid=3D35375
> > View this thread: http://www.excelforum.com/showthread.php?threadid=3D5=
61410
> >

Ardus:

Sorry, your post is much easier than mine, I got carried away and made
it complicated.

It obviously doesnt go into an indefinate loop because you are not
selecting, I selected as from experiance when i put now() in it updated
so I had to paste as value, but in this case is not needed.

maybe mine is a little too complicated.......

Duncan


Ardus Petus wrote:

> Hi Leo,
>
> Paste the following code in your worksheet's code (right-click on tab,
> select View code)
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.EnableEvents =3D False
> Range("A1").Value =3D Date
> Application.EnableEvents =3D True
> End Sub
>
>
> HTH
> --
> AP
>
> "leonidas" <[email protected]> a =E9c=
rit
> dans le message de news:
> [email protected]...
> >
> > Hi,
> >
> > I have a worksheet in excel with a lot of data on it. The data exists
> > of text, numbers and formulas.
> > I also have a date which should be updated to todays date when any of
> > the cell on the worksheet is changed.
> > Is it possible to do this and how should the code look like?
> > Thanks in advance for helping me!
> >
> >
> > --
> > leonidas
> > ------------------------------------------------------------------------
> > leonidas's Profile:
> > http://www.excelforum.com/member.php...userid=3D35375
> > View this thread: http://www.excelforum.com/showthread.php?threadid=3D5=
61410
> >