VBA Excel Msgbox keeps coming out when clicking on any other cell

Question

I have the Msgbox set in my Excel sheet b the following code:

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Range("D32").Value > 12 And Range("D32").Value < 25 Then
 VBA.MsgBox "Remember to double up on cable lengths and DP's", , "THP Between 13 & 24"
 End If

 End Sub

it works, but pop-ups every time when I clik on any other cell in my worksheet.

I tried to sort this out by using this thread:

VBA MsgBox keeps coming back

and my code was looking like this:

  If Range("D32").Value > 12 And Range("D32").Value < 25 Then
         If MsgBox("Remember to double up on cable lengths and DP's", , "THP Between 13 & 24") Then
        Application.Undo
        End If
   End If

but it didn't work.

I found also other solutions here:

http://www.vbaexpress.com/forum/showthread.php?35907-Solved-display-msgbox-once

https://www.mrexcel.com/board/threads/vba-to-display-msgbox-only-once.806009/

stackoverflow.com/questions/49072954/vba-msgbox-show-only-once

but they weren't helpful.

Is there any way to make it work? I would like to have this msgbox appear just once.

You have to test the `Target` parameter to see if it is a cell that you want to pop-up for. You are only testing the values of 2 specific cells. — RBarryYoung, Jun 10 '22 at 15:32
I have already target set https://stackoverflow.com/questions/19811227/how-to-use-the-target-parameter-in-a-standard-module — Geographos, Jun 13 '22 at 08:11
The code you have posted is not testing the `Target` parameter. *You* are not supposed set `Target`, the event `Worksheet_SelectionChange` sets it for you and passes it to you. The `Target` parameter indicates the range of the new selection. The `Worksheet_SelectionChange` event fires for *every* selection change in the worksheet, so you have to test the `Target` parameter to see if it is in the range(s) that you want to handle and exit/skip if it is not. As your link indicates you could also check the `ActiveCell` global variable, but you are not doing that either in your posted code. — RBarryYoung, Jun 13 '22 at 13:48
@RBarryYoung thanks for clarification. I need to study this bit for the future. — Geographos, Jun 13 '22 at 14:00

score 1 · Answer 1 · edited Jun 13 '22 at 14:05

The correct code should be like this:

If Not Intersect(Target, Range("D32,D33")) Is Nothing Then

    If Range("D32").Value > 12 And Range("D32").Value < 25 Then
        VBA.MsgBox "Remember to double up on cable lengths and DP's", , "THP Between 13 & 24"
    End If

End If

If we want to have the msgbox appear just once, we have to set the range of intersection. This range surely will be restricted to our target cell and optionally the next cell underneath.

Obviously as said in the comment below, we must enclose everything within:

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)

https://www.myonlinetraininghub.com/excel-forum/vba-macros/message-box-should-appear-only-once-when-spreadsheet-is-opened

VBA Excel Msgbox keeps coming out when clicking on any other cell

1 Answers1