MsgBox appearing multiple times when vba is executed-CodePudding

I am using a Msgbox in if else condition. When I use other conditions alongwith MsgBox, the MsgBox pops up multiple times and I have to end the program. Following is my code in module

Sub CheckValue(Target)
If Target.Offset(0, 12) < 1 Then
     MsgBox "This is a sample box"
     Range(Target.Offset(0, -12), Cells(Target.MergeArea(1, 1).Row, Target.MergeArea(1, 1).Offset(1, -2).Column)).ClearContents
     Target.Offset(0, 0).ClearContents
     Target.Offset(-4, 0).Select
     End If

I activate this sub through worksheet change. The code is as follows:

  Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$N$16" Then

    Call CheckValue(Target)
   End If
   End Sub

Help appreciated

CodePudding user response：

As you clear contents in your CheckValue sub, you are triggering the change-event.

You have to add Application.EnableEvents

Sub CheckValue(Target)
If Target.Offset(0, 12) < 1 Then
     MsgBox "This is a sample box"

     Application.EnableEvents = false  '--> disable event
         Range(Target.Offset(0, -12), Cells(Target.MergeArea(1, 1).Row, Target.MergeArea(1, 1).Offset(1, -2).Column)).ClearContents
         Target.Offset(0, 0).ClearContents
     Application.EnableEvents = true    '--> enable events
     
     Target.Offset(-4, 0).Select

     End If
end sub