Highligh date spreadsheet last updated

[NotOnUrNelly]

Hi All

I am trying to highlight in an excel the date the any changes where made to the spreadsheet.

IF the spreadsheet was opened and just looked at the date would not change

If any changes where made to the spreadsheet. I need the cells date and time to update accordingly.

I have tried the following simple code below

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sheets("CM_2003-2004&quot.Range("BP1&quot.Value = Now
End Sub

but this only seems to work for changes in text. In some instances cell color will change etc. (in the case of this the date will not update)

Many thanks for your help
Jamie

 

[RobBroekhuis]

You're running into one of the limitations of Excel's events - to the best of my knowledge, there is no way of capturing formatting changes. If you wanted to, really badly, you could keep track of (a subset of) the current cell's formatting properties, and use the _selectionchange event to see if anything changes. That's probably more work than it's worth, though...
Rob

 

[NotOnUrNelly]

Thanks for your reply rob

I am almost certin I have read in an excel book, something to do with saving workbooks (the page was highlighting how to save workbooks) the code actually detected whether any changes had been made and saved accordingly.

Dunno

Thanks Again

 

[xlbo]

spot on Rob - I would fully agree
'Fraid your hook says it all about this request...NotOnUrNelly Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[RobBroekhuis]

Aha - I see what you're saying. Yes, you can use the _selectionchange event to see if ANY change has been made:

sub worksheet_selectionchange(...)
if not activeworkbook.saved then
Sheets("CM_2003-2004&quot.Range("BP1&quot.Value = Now
activeworkbook.saved=true
AnyChanges=true
end if
end sub

This, however, has the nasty side effect of allowing the user to close the workbook without being prompted to save changes. To get around this, you also need to put in a workbook_beforeclose event sub, which checks if any changes have been made:

sub workbook_beforeclose
if AnyChanges then
if msgbox(&quot;This workbook has been changed. Save?&quot;,vbYesNo)<>vbNo then
activeworkbook.save
end if
end if
end sub

and, for good measure, a _beforesave handler which resets the AnyChanges variable to False. Convoluted, eh?


Rob