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?
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?