Run macro on cell update

[SueKi]

I'm building a model to show a bunch of data and a chart for the item selected in a validation drop down list. When I select a new item, I need it to clear a few fields to start the next item. I've done simple macros, but just found out worksheet code exists from a old question on here. So, I've added the following code to my worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("b3")
Call ClearScenario

End Sub

This is copied from the thread I found, but I updated the range to B3 and changes the Call Name to my simple macro to clear fields, that looks like this:

Sub ClearScenario()
'

Range("H6").Select
Selection.ClearContents
Range("H11").Select
Selection.ClearContents
Range("H9").Select
Selection.ClearContents

End Sub

When I change the item in the validation filter, either file crashes immediately, or it repeatedly looks the ClearScenario (saved to the workbook) until I hit ESC, then it crashes. When I re-open, it says my file is corrupt.

I tried removing the call macros bit and just adding clearing my cells to the worksheet code, but I got the same result - File and Excel closed, on re-open it said my file is corrupt.

What am I doing wrong?

 

[SueKi]

Googling this some more, I tried multiple variations and this one works!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Application.Run "Module1.ClearScenario"
End If
End Sub

The final change was to use B3 instead of b3, not sure how many prior versions would have worked if it weren't for lazy typing :-\

 

[Snakehips]

@SueKi
The Worksheet_Change event code is called every time a worksheet change occurs. Your code is only setting the Variable KeyCells to range B3 and then calling your Macro.
The setting of that variable serves no purpose. So effectively that code just calls your macro. As and when your code changes a cell, it calls itself again and ends up in an infinite loop that Excel cannot deal with.

Try something like below.

Private Sub Worksheet_Change(ByVal Target As Range)

'Ignore if change is to more than single cell
If Target.Count > 1 Then Exit Sub
'Ignore if Target cell is NOT B3
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
'otherwise
'Disable the calling of events to prevent any Claer actions calling this event and an infinite loop

Application.EnableEvents = False
'call your sub
Call ClearScenario
'Sub returns here after completion
'Reenable events
Application.EnableEvents = True

End Sub

Hope that helps.

 

[SueKi]

@SueKi
The Worksheet_Change event code is called every time a worksheet change occurs. Your code is only setting the Variable KeyCells to range B3 and then calling your Macro.
The setting of that variable serves no purpose. So effectively that code just calls your macro. As and when your code changes a cell, it calls itself again and ends up in an infinite loop that Excel cannot deal with.

Try something like below.

Private Sub Worksheet_Change(ByVal Target As Range)

'Ignore if change is to more than single cell
If Target.Count > 1 Then Exit Sub
'Ignore if Target cell is NOT B3
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
'otherwise
'Disable the calling of events to prevent any Claer actions calling this event and an infinite loop

Application.EnableEvents = False
'call your sub
Call ClearScenario
'Sub returns here after completion
'Reenable events
Application.EnableEvents = True

End Sub

Hope that helps.

I understand the problem now, thank you!