2

I have simple macros for clearing cells on "Sheet1", which have drop down lists.

Sub reset1()

    Range("D20:E21").ClearContents
    Range("D8:E9").ClearContents
    Range("D6:E7").ClearContents

End Sub

Sub reset2()

    Range("D20:E21").ClearContents
    Range("D8:E9").ClearContents
End Sub

Then I call these macros on "Sheet1" if the cell values change

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$4" Then
        Call reset1
    End If
    If Target.Address = "$D$6" Then
        Call reset2
    End If
End Sub

This code is written on the "Sheet1".

Normally it works but sometimes reset1() doesn't work.

I should then save and reopen the excel or run the macro manually.

Should I better modify some codes?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Mien Yi
  • 45
  • 7
  • Does Reset1 call Worksheet_Change to run reset2? as D6 is changing in reset1? – Nathan_Sav Aug 05 '21 at 09:04
  • You should disable events while changing cells from a `Change` event. – Rory Aug 05 '21 at 09:08
  • I agree with @Rory. You may want to see this [Working with Worksheet Change Event](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) – Siddharth Rout Aug 05 '21 at 09:13

1 Answers1

1

First problem is that with Range("D20:E21") it is not clear in which worksheet that range should be. Always specify the worksheet like Worksheets("Sheet1").Range("D20:E21").

Second problem is that if you .ClearContents in a Worksheet_Change event this is a cell change and triggers another Worksheet_Change event and so on. So it is recommended to disable events Application.EnableEvents = False before changing cells in Worksheet_Change event.

Third problem is that if you test Target.Address = "$D$4" and you copy paste a range where D4 is included your code will not run even if your cell D4 changed. Therefore you always need to work with Intersect.

Option Explicit

Sub Reset1(ByVal ws As Worksheet)
    ws.Range("D20:E21,D8:E9,D6:E7").ClearContents
    ' alternative:
    ' Union(ws.Range("D20:E21"), ws.Range("D8:E9"), ws.Range("D6:E7")).ClearContents
End Sub

Sub Reset2(ByVal ws As Worksheet)
    ws.Range("D20:E21,D8:E9").ClearContents
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error Goto ENABLE_EVENTS  ' in any case an error happens make sure events are enabeld again
    If Not Intersect(Target, Me.Range("D4")) Is Nothing Then
        Reset1 Me  ' here we tell Reset1 to take `Me` as worksheet. Me refers to the worksheet `Target` is in.
    End If
    
    If Not Intersect(Target, Me.Range("D6")) Is Nothing Then
        Reset2 Me
    End If

ENABLE_EVENTS:
    Application.EnableEvents = True
    If Err.Number Then
        Err.Raise Err.Number
    End If
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73