PDA

View Full Version : Help: Worksheet_change event disable "undo"?



yurble_vn
09-12-2007, 10:01 AM
I have the following code in a worksheet to control change event, but when ever I try to copy cell by drag, i can not use autofill option: copy format only, or copy without format.

When check the undo icon, it is faded. Do the code disable undo?

is there anyway to use event code but still can use auto fill option?

Please help

Please help
Private Function IsOpen(FileName As String) As Boolean
' Determine if a workbook is open or not
Dim wb As Workbook
For Each wb In Application.Workbooks
If UCase(wb.Name) = UCase(FileName) Then
IsOpen = True
Exit Function
End If
Next wb
IsOpen = False
End Function
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error Resume Next
If Not Intersect(Target, Range("A1:G300")) Is Nothing Then
Range("I11").Interior.ColorIndex = 3
End If
If Not Intersect(Target, Range("J1:J6")) Is Nothing Then
Range("I11").Interior.ColorIndex = 3
End If
If Not Intersect(Target, Range("L1:R300")) Is Nothing Then
Range("I11").Interior.ColorIndex = 3
End If
End Sub

mdmackillop
09-12-2007, 10:14 AM
Any changes made by a macro cannot be undone with the Undo button or Ctrl Z. For this reason, it is sometimes desirable to build in an "Are you sure?" messagebox, before irreversible changes are made.
e.g.
Sub DeleteRows()
If Application.CountA(Selection.EntireRow) > 0 Then
If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2) = vbYes Then
Selection.EntireRow.Delete
End If
End If
End Sub

mdmackillop
09-12-2007, 10:20 AM
Re your detailed question, Can you clarify what you are trying to do. I've not found any problems.

yurble_vn
09-13-2007, 12:37 AM
my problems is:
I make an event, when ever there is any changes in column A:A, cells I11 will be red.

When I fill in Cell A1, then drag it to copy to all cell in A2:A12, it copy the format too. But I just want to copy number only,

And because i can not undo, so can not use Auto Fill Option to adjust to: copy without format

unmarkedhelicopter
09-13-2007, 01:09 AM
This is normal behaviour, don't drag, if you don't want it red.
Or just reset colour of destination back to xlNone