Can't Undo after cell change (worksheet_change)

[mknowles]

I'm sending data requests out for updating and would like to know which cells have been changed/updated. From the forums, I found the following VBA code which does what I want. What I've noticed is that if I modify a cell I don't want to, I can't undo it (Ctrl-Z doesn't work and Edit - Undo is grayed out). As someone not familiar with VBA, is there something in the code that prevents undo, or something else going on?

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 4
End Sub

 

[Smitty]

Welcome to the Board!

You can undo after a change event, but it takes some work. See John Walkenbach's UNDO Method: http://spreadsheetpage.com/index.php/tip/undoing_a_vba_subroutine/

Hope that helps,

 

[VoG]

Running code disables undo. You need specially written code to give you an undo feature - see http://www.j-walk.com/ss/excel/tips/tip23.htm

 

[mknowles]

Thanks for the info. I've been messing around with the undo subroutine and have gotten it to work as written and following the example. I'm trying to adapt it to my spreadsheets and have a few questions.

1 -- Is sub ZeroRange necessary? It seems like it's only there to the change initially defined cells to 0. If the user changes a cell to a value other than 0, does ZeroRange need to be modified and retained?

2 -- Does sub UndoZero reference anything in ZeroRange? It seems to reference SaveRange but when I remove ZeroRange I get the "Can't undo" message box, which makes me think that ZeroRange is needed.

Thanks,

 

[Boller]

http://www.mrexcel.com/forum/showthread.php?t=251651&highlight=undo