Is this a loop?

[bw]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim NewVal, ValTyped
Target.Select
ValTyped = ActiveCell
If Len(ValTyped) = 0 Then
NewVal = ""
ActiveCell = NewVal
Else
NewVal = "Test"
ActiveCell = NewVal
End If
End Sub

When I step through this code, it begins at "Private" and goes down to one of the
"ActiveCell = NewVal" statements, and then back to "Private" again...over and over and
over again.

Can someone explain what's happening here? Is my code wrong?

Thanks,
Bernie

 

[Chip Pearson]

Bernie,

What you are experiencing is called "recursion". That occurs
when the calling procedure calls itself, which calls itself,
which calls itself, and so on. This is happening because of the
line "ActiveCell = NewVal". This changes the value of the active
cell, which causes the Change event to occur, which changes the
active cell, which causes the Change event to occur, and so on.

You will want to disable events in your change procedure to
prevent it from calling itself. E.g.,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim NewVal, ValTyped
Application.EnableEvents = False
Target.Select
ValTyped = ActiveCell
If Len(ValTyped) = 0 Then
NewVal = ""
ActiveCell = NewVal
Else
NewVal = "Test"
ActiveCell = NewVal
End If
Application.EnableEvents = True
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com