Macro call on delete

[MarkReddell]

I can make my macro call work with an entry of zero, however, I would like to make it work upon "Delete"!!! Any Suggestions???

 

[JamesW]

Hi there,

Does this help?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        If IsEmpty(Target.Value) Then MsgBox "deleted"
    End If
End Sub

 

[p45cal]

What is the current 'macro call' mechanism for an entry of zero? Is it in an event handler?

 

[MarkReddell]

I have been out of pocket since early this morning! My apologies 2 both p45Cal & JamesW 4 not replying sooner!!! My thanx 2 ALL of U out there that have been where I am with trying 2 get it right with the VBA Code. Maybe with the help like I have been receiving, I can get 2 where U R so maybe I can help someone else someday!!! Thanx Again!!!!!! Now back to your question: this my code: The only problem I'm having is that in the case A18; when I delete Cell A18, my macro didn't run!!! Everything else run good!!! Any suggestions???

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A1:A4,A17,A18,A19,B2,C7:F7,G4")) Is Nothing And _
Target.Value <> "" Then

Application.EnableEvents = False

Select Case Target.Address(0, 0)

Case "A1"
If UCase(Target.Value) = "MAYBE" Then
CLEAR_TRADE_INFO
Else
SORT_LIST
End If

Case "A18"

If Not Intersect(Target, Range("A18")) Is Nothing Then
If IsEmpty(Target.Value) Then
COMPARE_DIFFERENT_TERMS
Else
COMPARE_SAME_TERMS
End If
End If
Case "A2", "A3", "A4", "C7", "D7", "E7", "F7", "A19"
SORT_LIST

Case "G4"
Select Case Target.Value
Case 1: ONE_SCENARIO
Case 2: SHOW_TWO_SCENARIOS
Case 3: SHOW_THREE_SCENARIOS
Case 4: SHOW_FOUR_SCENARIOS
End Select

Case "A17"
If UCase(Target.Value) = "SHOW_TERMS" Then
SHOW_TERMS_ONLY
ElseIf UCase(Target.Value) = "DONT_SHOW" Then
DONT_SHOW_TERMS_ONLY
End If

Case "B2"
Application.Run Target.Value

End Select

Application.EnableEvents = True
End If

End Sub

 

[p45cal]

your problem is with:
And Target.Value <> "" Then
because when you delete A18 it is equal to "". So it's never getting to:
If Not Intersect(Target, Range("A18")) Is Nothing Then
There are many ways to get round this. One is to move the End If associated with the first If up before the above line (with a few other adjustments).

 

[MarkReddell]

Thanx p45Cal 4 ALL your help!!! Have a Blessed Day & a Merry CHRISTmas!!!