VBA code disables Undo command

[ktoth04]

The following code updates a 'date edited' column in a worksheet we use.
However, it also seems to disable the undo command. I understand not being
able to edit the changes vba makes, but you cant undo an edit you make. this
is probably because vba is making a change after everything you edit. Is
there any way to re-enable Undo?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Count > 1 Then
Exit Sub
Else
If Right(Target.Address, 2) = "$1" Then Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With Me
If Left(Target.Address, 3) = "$A$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$B$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$C$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$E$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$F$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$G$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$H$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$I$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$J$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$K$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$L$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$M$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$N$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$O$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$P$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$Q$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$R$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$S$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$T$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$U$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$V$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$W$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$X$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$Y$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$Z$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AA$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AB$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AC$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AD$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AE$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AF$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AG$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AH$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AI$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AJ$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AK$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AL$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AM$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AN$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AO$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AP$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AQ$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AR$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AS$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AT$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AU$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AV$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AW$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AX$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AY$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
ElseIf Left(Target.Address, 3) = "$AZ$" Then
With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
End If
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Exit Sub
errHandler:
MsgBox Err.Number & " " & Err.Description
Application.EnableEvents = True
End Sub

[Rick Rothstein (MVP - VB)]

Just want to make a couple of comments about your code. First, when you
reach this line of code....

ElseIf Left(Target.Address, 3) = "$AA$" Then

it, and all remaining ElseIf tests are incorrectly checking the left **3**
characters to see if they equal the **4** characters in quotes after the
equal sign. I'm guessing you mean to test the left 4 characters. Now, with
that said, I am pretty sure that **all** your code can be replaced with the
following...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler

If Target.Count > 1 Or Right(Target.Address, 2) = "$1" Then Exit Sub

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

If Target.Address Like "$[A-Z]$" Or Target.Address Like "$A[A-Z]$" Then

With Range("D" & Target.Row)
.Formula = "=NOW()"
.Calculate
.Value = .Value
End With
End If

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

errHandler:
If Err.Number > 0 Then MsgBox Err.Number & " " & Err.Description
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Rick

"ktoth04" <kto...@discussions.microsoft.com> wrote in message
news:AB7B071A-4282-45B3...@microsoft.com...

[Dave Peterson]

You have to keep track of things yourself.

John Walkenbach shows how:
http://j-walk.com/ss/excel/tips/tip23.htm

--

Dave Peterson

[ktoth04]

You're correct that it doesn't work after $AA$. This would be because I
modified existing code and assumed the values between the $ signs were column
labels. Are they not? Anyway, your code doesn't do what I'm trying to do
(actually, it appears to do nothing on my spreadsheet, but i'm not sure that
is accurate), which is to replace the values in column D with the value of
the function NOW, whenever something in the same row (in columns A-AZ, but
not including column D) is edited. (ie, I want them to be able to manually
reset the date in an entry should they so desire, but whenever they make any
changes, it will be autoset to NOW).

[ktoth04]

That was very helpful tip, however, I don't necessarly want to undo the
actions taken by the script, but the actions taken that triggered the script,
would you have any suggestions for keeping track of that?

[Rick Rothstein (MVP - VB)]

Sorry, I had left out the asterisks from the pattern strings in the Like
comparisons. However, now that I understand what you are trying to do, here
is a slightly more streamlined version of the code...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler

Application.EnableEvents = False
With Target
If .Count > 1 Then Exit Sub
If .Address Like "$[A-CE-Z]$*" Or .Address Like "$A[A-Z]$*" Then
Range("D" & .Row).Value = Now
End If
errHandler:
Application.EnableEvents = True

If Err.Number > 0 Then MsgBox Err.Number & " " & Err.Description

End With
End Sub

Note that this code allows the user to change the values in Column D by
simply typing in something new there (which your original code allowed also
I think). I can give you code to stop that if you would like.

Rick

"ktoth04" <kto...@discussions.microsoft.com> wrote in message

news:6DFDDA5B-CF28-421A...@microsoft.com...

[ktoth04]

Thanks very much, that works perfectly! And I want them to be able to edit
the column should they want to, atleast for now. Thanks again!

[Dave Peterson]

Nope.

I guess you could keep track of everything you do to the workbook--but that
sounds pretty unreasonable to me.

--

Dave Peterson

[ktoth04]

If I were to modify that code for my script, would it have the stored undo
for the action before the script ran? Or just the undo I created that undoes
the script, and then no more?

[Dave Peterson]

Nope. The previous stuff would be lost.

--

Dave Peterson

[ktoth04]

T.T

That's sad... Do you know any way to access the queue of Undo commands so
that I could store the queue and then restore the queue at the conclusion of
the script? Maybe I should post another thread

[Dave Peterson]

Nope. I don't know of a way of finding the details of the undo's.

--

Dave Peterson