My Worksheet_Change is only partially working and I don't know why...

[jmpatrick]

The macro that is supposed to run when data in the range CalendarFloorsInvoiceAmountColumn (FloorOrderNumber) only works if I delete the contents of a cell, not when I enter data in an empty cell in the named range. I can't figure out why but I'm sure it'll be embarrassing.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next

    If Not Intersect(Target, Range("ElevationColumn")) Is Nothing Then

        Application.EnableEvents = False

        Target = UCase(Target)

        Application.EnableEvents = True

    End If
    
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next

    If Not Intersect(Target, Range("SubLotColumn")) Is Nothing Then

        Application.EnableEvents = False

        Target = UCase(Target)

        Application.EnableEvents = True

    End If

    On Error GoTo 0

    Dim KeyCells As Range
    Set KeyCells = Range("DateColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
    
    Call ChangeDate
    
    Application.EnableEvents = True
    
    End If
       
    Set KeyCells = Range("CalendarFloorsInvoiceAmountColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
    
    Call FloorOrderNumber
    
    Application.EnableEvents = True
    
    End If
    
End Sub

 

[GWteB]

It works for me. To find out the cause set a breakpoint on your event handler en step through the code after entering data in an empty cell.

 

[Marc L]

​

First remove all On Error codelines in order you can see where the logic may fail …​

 

[jmpatrick]

I figured this out! I was triggering the macro by entering data. When I hit enter it selected the next cell down as the starting point for the code, which wasn't what I wanted. Easy fix!

Thanks for your help.

 

[GWteB]

You are welcome and thanks for letting us know