Worksheet_Change event

[blatham]

What is the best way of identifying if the worksheet change event was triggered by anything other than a change in the contents of a cell?

I want to be able to handle cell changes but am not interested in other events such as inserting/deleting rows etc and want to exit the sub in these situations.

The solution presumably involves some sort of test on the 'target' range. Any ideas?

 

[parmel]

This is crude but it's a start. If rows or columns have been added or deleted then no action is taken. I don't know what other changes you want to ignore but you might be able to use variations of the time.

If Target.Cells.Count = 256 Or Target.Cells.Count = 65536 Then exit sub

 

[blatham]

That would enable me to capture insertions/deletions for rows and columns but not for cells.

Any other ideas?

 

[parmel]

Tell me if the following code will do what you want. It ignores cell additions or deletions. Where I have Msgbox "hello" is where you'd put the code you want to happen if the change is not a cell addition or deletion.
All this code goes in the ThisWorkbook object.


Option Explicit
Dim bStop As Boolean


Private Sub Workbook_Open()
bStop = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Static iCount As Integer, rFirst As Range
Dim bEndIt As Boolean

If bStop = True Then
bStop = False
End
End If
iCount = iCount + 1
If iCount = 1 Then
Set rFirst = Target
myundo
End If

If iCount = 2 Then
If Not IsObject(rFirst) Then
bEndIt = True
myredo (bEndIt)
End If
Dim bTest As Boolean
bTest = Target Is rFirst

If (bTest = False) And (rFirst.Address <> Target.Address) Then
bEndIt = True
myredo (bEndIt)
End If

bEndIt = False
myredo (bEndIt)
MsgBox "hello"
End If



End Sub

Sub myundo()
Application.Undo
End Sub

Sub myredo(bEnd As Boolean)
If bEnd = True Then bStop = True
Application.Undo
End Sub

 

[parmel]

I've modified the code I provided above so that any changes you make to the worksheet as a result of the initial changes will not cause any problems.

I've added a procedure called newcode where you can add the code you want to execute. I hope this is what you had in mind.

Option Explicit
Dim bStop As Boolean


Private Sub Workbook_Open()
bStop = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Static iCount As Integer, rFirst As Range
Dim bEndIt As Boolean

If bStop = True Then
bStop = False
End
End If
iCount = iCount + 1
If iCount = 1 Then
Set rFirst = Target
myundo
End If

If iCount = 2 Then
If Not IsObject(rFirst) Then
bEndIt = True
myredo (bEndIt)
End If
Dim bTest As Boolean
bTest = Target Is rFirst

If (bTest = False) And (rFirst.Address <> Target.Address) Then
bEndIt = True
myredo (bEndIt)
End If

bEndIt = False
myredo (bEndIt)
newcode
End If

End Sub

Sub myundo()
Application.Undo
End Sub

Sub myredo(bEnd As Boolean)
If bEnd = True Then bStop = True
Application.Undo
End Sub

Sub newcode()
Application.EnableEvents = False
'place the remainder of your code here
Application.EnableEvents = True
End Sub