How to fix msgbox showing up twice When Cell Formula Result Changes?

Question

I want a msgbox to show up only once when formula result changes in my table range ("K2:K5"). Right now it shows twice.

In this range I have excel-formulas. Formula: H12*10

These formulas is refering to a dropdown-list (a list that I've created from "data validation" on the excel menu Data-tab).

The dropdown-list is located in cell H12. The values in the dropdown is refering to range(D15:D17)

I've noticed though that the msgbox shows up once when I remove the dropdown and manualy type in values in H12.

Thankful for any help on this

Private Sub Worksheet_Calculate()
Dim Xrg As Range
Set Xrg = Range("K2:K5")
If Not Intersect(Xrg, Range("K2:K5")) Is Nothing Then
    MsgBox "Hi"
End If
End Sub

I have also tried to add Application.enableEvents to the code but no success.

Private Sub Worksheet_Calculate()
Dim Xrg As Range
Set Xrg = Range("K2:K5")
Application.EnableEvents = False
If Not Intersect(Xrg, Range("K2:K5")) Is Nothing Then
    MsgBox "Hi"
End If
Application.EnableEvents = True
End Sub

Answer 1

Your current code has no added value on a Worksheet_Calculate event due to comparing two exact same ranges and see if they intersect. That would always be the case. Your code would have the same effect as:

Private Sub Worksheet_Calculate()
    MsgBox "Hi"
End Sub

Might you have more functions in your worksheet that could trigger this event unwanted outside range K2:K5, you should look into the Worksheet_Change event and return a messagebox when your referenced cell has changed value.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$H$12" Then MsgBox "Hi"
End Sub

Choose either one, but don't use both cause you'll end up with two msgbox's