Codes for ClearContents and byPass Worksheet_Change RRS feed

  • Question

  • I have a worksheet with Worksheet_Change codes that auto populate cells in the same row when user selects a drop down in column1.  For example, if users selects, "Job1" in Range("A1") then VBA looks up corresponding billing codes, service items, etc and populates Range ("B1", "C1", etc).

    The problem I'm running into is trying to create similar codes to delete everything in the auto populate cells if the user deletes a selection in column A.  I end up in a loop since it goes back to the Workseet_Change codes when something changes.  

    Hoping someone has ran into similar situation and have found a fix. 

    Thanks in advance.

    Excel 2013 & Windows 10


    Roget Luo

    Tuesday, April 11, 2017 7:43 PM


  • Turn off events before you change anything on the sheet - for example:


    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        If Intersect(Target, Range("A2:A10")) Is Nothing Then Exit Sub

        'Turn off events to keep out of loops
        Application.EnableEvents = False

        'Code to do stuff here

        Target.Offset(1,1).Value = Date
        'Turn on events to re-enable your code
        Application.EnableEvents = True
    End Sub

    Tuesday, April 11, 2017 8:14 PM