Timestamp cell change??!! no undo? 2

[VulcanJedi]

An associate wants to datestamp any changes made to certain data....I have wrote the following:
This works, but disables the UNDO command, obviously risking data integrity any insights? When does the UNDO command get disabled?


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 11 Then
Target.Next = Date
End If



End Sub Tranpkp

 

[SkipVought]

Hi,

UNDO is NOT off. Its just that UNDO only works for stuff thats NOT done in code -- stuff that the user does DIRECTLY.

The DATASTAMP is not done DIRECTLY by the user.

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RobBroekhuis]

You can write your own undo handler, although I haven't tried to use it in combination with an event handler. An interesting proposition...
Rob

 

[VulcanJedi]

I was thinking of that but ran into some philosphical obstacles Rob! Tranpkp

 

[SkipVought]

Yes, if you store the target.value from the Worksheet_SelectionChange event, which occurs just before a worksheet_Change might occur, a SpecialUndo programmer defined button could replace the stored value (a one-step UNDO) Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RobBroekhuis]

I played around with this a little bit, and came up with the following:

-- in the worksheet code module --
Private Sub Worksheet_Change(ByVal Target As Range)
Set StampCell = Target.Offset(0, 1)
OldStampCellValue = StampCell
Application.EnableEvents = False
Target.Offset(0, 1) = Date
Application.EnableEvents = True
Application.OnUndo "Undo timestamp", "unstamp"
End Sub

-- in a general code module --
Public StampCell As Range
Public OldStampCellValue
Sub unstamp()
Application.EnableEvents = False
StampCell = OldStampCellValue
Application.EnableEvents = True
End Sub

This works. That is, it puts the correct entry in the edit menu, which, when selected, undoes what the timestamp event does automatically. Unfortunately, once it's done that, it doesn't allow the previous undo, so it's no use at all. Oddly, it seems like the unstamp macro itself (because it has no .onundo method) disables the undo stack. Since this would be counter to any useful undo functionality, I must be missing something. Unfortunately, I don't have any Excel VBA mega-handbook with examples that could set me straight. Luckily, I have Tek-Tips! What's wrong with the code above?
Rob

 

[VulcanJedi]

How were you able to implement such an approach? Tranpkp

 

[SkipVought]

Rob,
Great move!!!
Application.OnUndo "Undo timestamp", "unstamp"

My vote for a STAR
Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VulcanJedi]

This actually does not work?? Tranpkp

 

[RobBroekhuis]

With Excel you should be able to do multiple undo's. I can make it work for the first one (from the change event), but can't get back to the previous ones (which, realistically, are the more important ones, corresponding to real user entries). It should be easy, but I couldn't find a workaround. Must admit I haven't used OnUndo much in my own work.
Rob

 

[VulcanJedi]

Nor I Rob, but great to be aware of it now. It just doesn't seem to capture when an existing value is changed.... Tranpkp