Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change format of a date field | Excel Worksheet Functions | |||
Automating grouping of Excel pivot field | Excel Discussion (Misc queries) | |||
Automating grouping of Excel pivot field | Excel Worksheet Functions | |||
I can't get a macro action to start from the current field? | Excel Discussion (Misc queries) | |||
I can't get a macro action to start from the current field? | Excel Discussion (Misc queries) |