CODEWITHSUNDEEP
excelvba
Mien Yi
Mien Yi

Reputation: 45

Should I better modify some codes for .ClearContents?

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?

Upvotes: 2

Views: 43

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 1

Related Questions