VBA code disables Undo command

K

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
 
R

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
 
K

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).

Rick Rothstein (MVP - VB) said:
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 said:
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)
Click to expand...
Click to expand...
 
K

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?
 
R

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 said:
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) said:
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 said:
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)
Click to expand...
Click to expand...
Click to expand...
 
K

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!

Rick Rothstein (MVP - VB) said:
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 said:
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) said:
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





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)
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Nope.

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

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?
 
D

Dave Peterson

Nope. The previous stuff would be lost.
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?
Click to expand...
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change_Event not responding to change made by formula. 11
VBA Coding Help for Beginner 0
Insert row gives run-time error 13 message 1
Double click fails to run the code, just selects the cell 12
Formula in macro gives "Run-time error '1004':" 1
Combine 2 worksheet event change codes 4
Remove Identical words 0
Macro autofills formula when a cell value is changed 4

Top