Delete creates error during Change event

  • Thread starter Thread starter Random
  • Start date Start date
R

Random

Why would deleting the values of a range of cells cause an error
during a simple Worksheet_Change event?

sample that causes error:

If Target.Value = 1500 Then

Application.ScreenUpdating = False
For i = 1 To 1000
Target.Offset(i, 0).Select
Selection.Value = i
Next
End If

Still very new to Excel VBA, so any help is greatly appreciated.

Thanks,

Random
 
Random,

What error are you getting? You should disable events if you are changing
cell values in the _Change event. If you don't, the change event changes a
value, which calls the Change event, which changes a value, which calls the
Change event, and so on and so on. Your code should look like

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
' rest of code
'
Application.EnableEvents = True
End Sub
 
Chip,

Your response answers another question I was going to ask... how to
stop the event from occuring on changes caused by the Change event.

The error that I am getting is

---------------------------
Run-time error '13':

Type mismatch
----------------------------

Could this possibly be due to deleting more than one cell at a time
and the target not really being a cell, but a range?

Random
 
Yes, if Target is more than 1 cell, then

If Target.Value = 1500 then

will cause a type mismatch error

If Target.count >1 then exit sub
If Target.Value = 1500 Then

Application.ScreenUpdating = False
For i = 1 To 1000
Target.Offset(i, 0).Select
Selection.Value = i
Next
End If
 
Hi Random

Like what Tom has mentioned, the error happens because the target has
more than 1 cells. Hence, the mismatched error will occur whenever a
change is made to a range of cells eg. copying a range of values to
the worksheet.

In the previous code provided by Tom, VBA will step out of the
worksheet_change event when you are dealing with more than one cell
change. In other words, if you tried copying a range of cells onto
the worksheet and even though one of copied values is 1500, your
written code to populate the next 1000 entries will not be run.

You may also consider :
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim i%
Dim cell As Object
For Each cell In Target
If cell.Value = 1500 Then
Application.ScreenUpdating = False
For i = 1 To 1000
Target.Offset(i, 0).Select
Selection.Value = i
Next
End If
Next cell
Application.EnableEvents = True
End Sub

This option will iterate all the cells in the target range and check
whether if any of the cell value equates to 1500.

Hopefully, the above info can be of help to ya. ^_^

Regards
Jo Lee

------------------------------------------------------------------------------
 
Back
Top