VBA - Message Box pop up twice

[Beatrice]

Hi experts, I need some help as the simple code for a pop up message always appear twice.
I want to process 3 checks and each of them might pop up once only
here is my code, could you please point out what did I do wrong?
Thanks in advance for your help.

Private Sub Worksheet_Calculate()
'popup message for part not found
Application.EnableEvents = True
Application.EnableEvents = False

If Range("h117").Value = 1 Then
MsgBox "Item 1 Not Found", vbExclamation, "Error"
End If

If Range("h145").Value = 1 Then
MsgBox "Item 2 Not Found", vbExclamation, "Error"
End If

If Range("h145").Value = 1 Then
MsgBox "Item 3 Not Found", vbExclamation, "Error"
End If

Application.EnableEvents = True
End Sub

 

[Craggs82]

It looks like your 3rd range is the same as your 2nd. They're both checking "h145".

 

[Beatrice]

It looks like your 3rd range is the same as your 2nd. They're both checking "h145".

Thanks, corrected the cell range, yet the problem still exist.
Pop up message twice.

 

[Craggs82]

i'll guess the popups are for different values not found? if you only need to return the first error it gets to (if any) then you'll need to nest your ifs -

Private Sub Worksheet_Calculate()
    'popup message for part not found
    Application.EnableEvents = True
    Application.EnableEvents = False
    
    If Range("h117").Value = 1 Then
        MsgBox "Item 1 Not Found", vbExclamation, "Error"
    ElseIf Range("h145").Value = 1 Then
        MsgBox "Item 2 Not Found", vbExclamation, "Error"
    ElseIf Range("h145").Value = 1 Then
        MsgBox "Item 3 Not Found", vbExclamation, "Error"
    End If
    
    Application.EnableEvents = True
End Sub

with how it's set up for you now, if two of you conditions succeed you will get 2 popups.

 

[Beatrice]

i'll guess the popups are for different values not found? if you only need to return the first error it gets to (if any) then you'll need to nest your ifs -

Private Sub Worksheet_Calculate()
    'popup message for part not found
    Application.EnableEvents = True
    Application.EnableEvents = False
   
    If Range("h117").Value = 1 Then
        MsgBox "Item 1 Not Found", vbExclamation, "Error"
    ElseIf Range("h131").Value = 1 Then
        MsgBox "Item 2 Not Found", vbExclamation, "Error"
    ElseIf Range("h145").Value = 1 Then
        MsgBox "Item 3 Not Found", vbExclamation, "Error"
    End If
   
    Application.EnableEvents = True
End Sub

with how it's set up for you now, if two of you conditions succeed you will get 2 popups.

thanks for helping, but it still pop up twice with these code.
H117/ H131 / H145 represent 3 different check, they need to be process separately
thank you

 

[Craggs82]

Well In that case the code is fine. I guess excel is recalculating after ok is hit the first time. As this sub triggers on the recalculate event. Try putting -

Application.calculation = xlmanual

At the beginning and -

Application.calculation = xlautomatic

At the end.
It's weird that it doesn't get stuck in a loop doing this. But hey it's excel.