ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VBA code disables Undo command (https://www.excelbanter.com/excel-programming/407315-vba-code-disables-undo-command.html)

ktoth04
VBA code disables Undo command
 
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\)[_1400_]
VBA code disables Undo command
 
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" wrote in message
...
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

Dave Peterson
VBA code disables Undo command
 
You have to keep track of things yourself.

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



ktoth04 wrote:

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

--

Dave Peterson

ktoth04
VBA code disables Undo command
 
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).

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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)

ktoth04
VBA code disables Undo command
 
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?

"Dave Peterson" wrote:

You have to keep track of things yourself.

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



ktoth04 wrote:

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()"

Rick Rothstein \(MVP - VB\)[_1401_]
VBA code disables Undo command
 
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" wrote in message
...
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).

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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)


ktoth04
VBA code disables Undo command
 
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!

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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).

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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)

Dave Peterson
VBA code disables Undo command
 
Nope.

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

ktoth04 wrote:

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?

"Dave Peterson" wrote:

You have to keep track of things yourself.

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



ktoth04 wrote:

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()"

--

Dave Peterson

ktoth04
VBA code disables Undo command
 
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" wrote:

Nope.

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

ktoth04 wrote:

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?

"Dave Peterson" wrote:

You have to keep track of things yourself.

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



ktoth04 wrote:

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)

Dave Peterson
VBA code disables Undo command
 
Nope. The previous stuff would be lost.

ktoth04 wrote:

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" wrote:

Nope.

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

ktoth04 wrote:

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?

"Dave Peterson" wrote:

You have to keep track of things yourself.

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



ktoth04 wrote:

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)

--

Dave Peterson

ktoth04
VBA code disables Undo command
 
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" wrote:

Nope. The previous stuff would be lost.

ktoth04 wrote:

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" wrote:

Nope.

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

ktoth04 wrote:

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?

"Dave Peterson" wrote:

You have to keep track of things yourself.

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



ktoth04 wrote:

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

Dave Peterson
VBA code disables Undo command
 
Nope. I don't know of a way of finding the details of the undo's.

ktoth04 wrote:

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" wrote:

Nope. The previous stuff would be lost.

ktoth04 wrote:

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" wrote:

Nope.

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

ktoth04 wrote:

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?

"Dave Peterson" wrote:

You have to keep track of things yourself.

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



ktoth04 wrote:

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

--

Dave Peterson


All times are GMT +1. The time now is 01:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com