Help with Marco


Posted by Paul B on February 13, 2002 1:59 PM

I have the code below to put an “a” in column A when you click in it, and to take it out if you click again, but it is doing away with the undo function, is there any way to change the code so this will not happen? Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, cell As Range
If Selection.Cells.Count = 1 And _
Not Intersect(Selection, Columns(1)) Is Nothing And _
Not Intersect(Selection, ActiveSheet.UsedRange) Is Nothing Then
With Selection
If .Value = "a" Then
.ClearContents
Else: .Value = "a"
End If
End With
End If
End Sub



Posted by Aumerle on February 13, 2002 5:37 PM

Bad news .......


When any macro is run, the Undo function is removed.
It is possible to retain the undo function when a macro is run (and not too difficult for your macro) - see http://j-walk.com/ss/excel/tips/tip23.htm
However, writing such code only undoes what the macro did and the undo function is not useable for changes before the macro was run.
This is not much use for your situation, since all that is required to undo what your macro does is to click on the cell again.