Pop up messages in excel when entering specific words into a cell

Question

Working off a macro I found on here, I managed to get a delivery requirement pop-up to work when entering a specific postcode. However, when I try and duplicate this for multiple postcodes with different delivery requirements I get 'Compile error: Block If without End If'.

First macro is the one that works for a single postcode & the second is the one where the error message appears (both have postcodes removed for GDPR).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:Z99")) Is Nothing Then
        If Target = "xxx xxx" Then
            MsgBox "HI-AB DELIVERY REQUIRED."
        End If
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:Z99")) Is Nothing Then
        If Target = "xxx xxx" Then
            MsgBox "HI-AB DELIVERY REQUIRED."
    If Not Intersect(Target, Range("A1:Z99")) Is Nothing Then
        If Target = "axx xx" Then
            MsgBox "NO DELIVERIES BEFORE 8AM."
        End If
    End If
End Sub

I only want the pop up to appear when the specific post code is entered. Is this possible?

Any help would be greatly appreciated!!

You need to get familiar with the If-Then-Else Statement. You may want to see [If Then Else Statement](https://youtu.be/JmT7Gv4K-d4) — Siddharth Rout, Oct 07 '22 at 09:15

Siddharth Rout · Accepted Answer · 2022-10-07T09:57:03.117

0

You need to get familiar with the If-Then-Else Statement.

Your code can be written as

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:Z99")) Is Nothing Then
        If Target = "xxx xxx" Then MsgBox "HI-AB DELIVERY REQUIRED."
        If Target = "axx xx" Then MsgBox "NO DELIVERIES BEFORE 8AM."
    End If
End Sub

or better

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:Z99")) Is Nothing Then
        Select Case Target
            Case "xxx xxx": MsgBox "HI-AB DELIVERY REQUIRED."
            Case "axx xx": MsgBox "NO DELIVERIES BEFORE 8AM."
        End Select
    End If
End Sub

edited Oct 07 '22 at 09:57

answered Oct 07 '22 at 09:13

Siddharth Rout

147,039
17
206
250

Thanks for the help! I'll be honest, I'm a rookie at best so I think some studying up is needed! I've tried the above & it works fine for the first target and message, however, when typing in the second one I don't get a message box – William Sidaway Oct 07 '22 at 09:41
The code that I gave you above just showed how to write the if then else. I have modified the code to do what you exactly want. you may have to refresh the page to see the changes – Siddharth Rout Oct 07 '22 at 09:59
You may want to restrict this to one cell with e.g. `If Target.Cells.CountLarge > 1 Then Exit Sub` to avoid a type mismatch error when more than one cell intersects (when copy-pasting multiple cells). – VBasic2008 Oct 07 '22 at 11:02
@VBasic2008: Yes you are right. I have explained about it in [Why MS Excel crashes and closes during Worksheet_Change Sub procedure?](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Oct 07 '22 at 11:05
...and I upvoted it ages ago when I hardly knew what events are. – VBasic2008 Oct 07 '22 at 11:07

Pop up messages in excel when entering specific words into a cell

1 Answers1