Adding If...Then to my Worksheet_Change?

[jmpatrick]

Good morning.

I have this code that runs a macro when the value in a specific column changes. I'd like to add an If...Then so the macro does NOT run if <ADD NEW> is the value. 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
    
    Set KeyCells = Range("CalendarModelColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
       
    Application.EnableEvents = False
                   
    Call Model
        
    Application.EnableEvents = True
    
    End If

End Sub

 

[Joe4]

You should be able to modify your exit line like this:

If Target.Cells.Count > 1 Or Target.HasFormula Or Target = "ADD NEW" Then Exit Sub

Just modify the text to match EXACTLY the way it looks in the cell.

 

[jmpatrick]

You should be able to modify your exit line like this:

If Target.Cells.Count > 1 Or Target.HasFormula Or Target = "ADD NEW" Then Exit Sub

Just modify the text to match EXACTLY the way it looks in the cell.

That works nicely!

Can that same idea be applied here?:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Target.Calculate
       
    Application.OnKey "{DELETE}"
       
    Dim KeyCells As Range
       
    Set KeyCells = Range("CalendarModelColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
    
    Application.OnKey "{DELETE}", "DeleteModel"
    
    Application.EnableEvents = True
    
    End If
           
End Sub

I tried this but it doesn't work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Target.Calculate
       
    Application.OnKey "{DELETE}"

    If Target = "<ADD NEW>" Then Exit Sub

    End If
       
    Dim KeyCells As Range
       
    Set KeyCells = Range("CalendarModelColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
    
    Application.OnKey "{DELETE}", "DeleteModel"
    
    Application.EnableEvents = True
    
    End If
           
End Sub

 

[Joe4]

Hmmm, that is really a different question altogether.
That code runs when a cell is selected (not changed).
Is the value in the cell selected really "<ADD NEW>"?

What is the purpose of the "Target.Calculate" line?
Do you not have automatic calculations turned on?

 

[jmpatrick]

Hmmm, that is really a different question altogether.
That code runs when a cell is selected (not changed).
Is the value in the cell selected really "<ADD NEW>"?

Could be. It's a drop down list. The first value in the list is <ADD NEW> which when selected opens a Userform. If the user selects <ADD NEW> and hits the Delete key I don't want the DeleteModel macro to run.

 

[Joe4]

It sounds to me like you do not need the "Worksheet_SelectionChange" code at all, which is triggered to run whenever a cell is just selected.
The "Worksheet_Change" code runs when the value is manually changed.

If I understand you correctly, it sounds to me like the "Worksheet_Change" is the only code you need here, and you can do away with the other code.