Automating a date field to change on certain action
 

I have a date in a cell on a spread sheet.
it is useed by no clacsz - it just is a date to tell
everyoon ethe last time the sheet was changed.

I would like to automate this date so that it changes upon
any change in the sheet being made.

If the sheet is just opened and accessed then I need the
date to stay put.



It is a 2 or three sheet workbook - usually two

2 people have access to read write this sheet and the rest
have read only

Thanks for your time

Automating a date field to change on certain action
 

Quite a commonly asked question.


Use the worksheet's change event to populate the date cell

Private Sub Worksheet_Change(ByVal Target As Range)
Static bUpdating As Boolean

If bUpdating Then
bUpdating = False
Else
bUpdating = True
Range("A1").Value = Format$(Date, "dd-mmm-yy HH:MM")
End If

End Sub


As the change is an event, setting adding the datestamp
will fire the event againg - hence the bolean flags this
and prevents an endless loop.

Change a cell
event fires
boolean is false, so set to true
add the date
event fires
the boolean is true, so reset it
end of second event
end of first event


HTH

Patrick Molloy
Microsoft Excel MVP





-----Original Message-----
I have a date in a cell on a spread sheet.
it is useed by no clacsz - it just is a date to tell
everyoon ethe last time the sheet was changed.

I would like to automate this date so that it changes
upon
any change in the sheet being made.

If the sheet is just opened and accessed then I need the
date to stay put.



It is a 2 or three sheet workbook - usually two

2 people have access to read write this sheet and the
rest
have read only

Thanks for your time
.

Automating a date field to change on certain action
 

Hey thank you for the time

I assume this goes in "this work book" or is it in a
module.

T
-----Original Message-----
Quite a commonly asked question.


Use the worksheet's change event to populate the date cell

Private Sub Worksheet_Change(ByVal Target As Range)
Static bUpdating As Boolean

If bUpdating Then
bUpdating = False
Else
bUpdating = True
Range("A1").Value = Format$(Date, "dd-mmm-yy HH:MM")
End If

End Sub


As the change is an event, setting adding the datestamp
will fire the event againg - hence the bolean flags this
and prevents an endless loop.

Change a cell
event fires
boolean is false, so set to true
add the date
event fires
the boolean is true, so reset it
end of second event
end of first event


HTH

Patrick Molloy
Microsoft Excel MVP





-----Original Message-----
I have a date in a cell on a spread sheet.
it is useed by no clacsz - it just is a date to tell
everyoon ethe last time the sheet was changed.

I would like to automate this date so that it changes
upon
any change in the sheet being made.

If the sheet is just opened and accessed then I need the
date to stay put.



It is a 2 or three sheet workbook - usually two

2 people have access to read write this sheet and the
rest
have read only

Thanks for your time
.

.

Automating a date field to change on certain action
 

Right click on the sheet tab and select view code. Put it there.

--
Regards,
Tom Ogilvy

"todd" wrote in message
...
Hey thank you for the time

I assume this goes in "this work book" or is it in a
module.

T
-----Original Message-----
Quite a commonly asked question.


Use the worksheet's change event to populate the date cell

Private Sub Worksheet_Change(ByVal Target As Range)
Static bUpdating As Boolean

If bUpdating Then
bUpdating = False
Else
bUpdating = True
Range("A1").Value = Format$(Date, "dd-mmm-yy HH:MM")
End If

End Sub


As the change is an event, setting adding the datestamp
will fire the event againg - hence the bolean flags this
and prevents an endless loop.

Change a cell
event fires
boolean is false, so set to true
add the date
event fires
the boolean is true, so reset it
end of second event
end of first event


HTH

Patrick Molloy
Microsoft Excel MVP





-----Original Message-----
I have a date in a cell on a spread sheet.
it is useed by no clacsz - it just is a date to tell
everyoon ethe last time the sheet was changed.

I would like to automate this date so that it changes
upon
any change in the sheet being made.

If the sheet is just opened and accessed then I need the
date to stay put.



It is a 2 or three sheet workbook - usually two

2 people have access to read write this sheet and the
rest
have read only

Thanks for your time
.

.