Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA code disables Undo command
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default 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



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default 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)
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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)

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default 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)
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default 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()"
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default 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)
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Where is the undo command in Excel 2007? kedawy Excel Discussion (Misc queries) 3 April 9th 09 04:46 PM
Macro disables "undo" feature..help please Terry Excel Worksheet Functions 6 February 21st 08 04:38 PM
Can't Undo Macro Command TKS_Mark Excel Programming 11 January 10th 08 11:56 PM
Can the Undo feature and/or command be turned off??? Sheri Excel Discussion (Misc queries) 1 October 11th 06 06:02 PM
Undo Command Button Actions Rob Excel Programming 1 September 9th 06 07:03 AM


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement