Message box is repeating with multiple worksheet changes

[scockster]

I have multiple worksheet event macros on my sheet. When selection Y/N is unlocks or locks respectively and a message box appears based on the selection. For some reason my macros 3-6 repeat the previous message boxes starting with the message box in the 2nd macro. The first and second macros do not repeat any message boxes. Not sure whats wrong. Here is my code. Change events occur in Columns H, I, T, Z, AF, AL. Can someone tell me why my messages are repeating from the previous macro first. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
My_Sub_A Target
My_Sub_B Target
My_Sub_C Target
My_Sub_D Target
My_Sub_E Target
My_Sub_F Target
End Sub


Public Sub My_Sub_A(ByVal Target As Range)
Dim R As Range, c As Range
Set R = Range("H5:H950")
If Not Intersect(Target, R) Is Nothing Then
Me.Protect Password:="123456", userinterfaceonly:=True
For Each c In Intersect(Target, R)
If c.Value = "N" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("H").Column).Locked = True
MsgBox "BETTER LUCK NEXT YEAR" & vbCrLf & "Now PAY Your dues!", vbInformation, "LOSER!!"
ElseIf c.Value = "Y" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("H").Column).Locked = False
MsgBox "YOU OWE $10 MORE DOLLARS!" & vbCrLf & "Good luck this week!", vbInformation, "PAY UP"
End If
Next c
End If
End Sub

Public Sub My_Sub_B(ByVal Target As Range)
Dim R As Range, c As Range
Set R = Range("I5:II50")
If Not Intersect(Target, R) Is Nothing Then
Me.Protect Password:="123456", userinterfaceonly:=True
For Each c In Intersect(Target, R)
If c.Value = "N" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("I").Column).Locked = True
MsgBox "BETTER LUCK NEXT YEAR" & vbCrLf & "Now PAY Your dues!", vbInformation, "LOSER!!"
ElseIf c.Value = "Y" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("I").Column).Locked = False
MsgBox "YOU OWE $15 MORE DOLLARS!" & vbCrLf & "Good luck this week!", vbInformation, "PAY UP!"
End If
Next c
End If
End Sub

Public Sub My_Sub_C(ByVal Target As Range)
Dim R As Range, c As Range
Set R = Range("T5:TI50")
If Not Intersect(Target, R) Is Nothing Then
Me.Protect Password:="123456", userinterfaceonly:=True
For Each c In Intersect(Target, R)
If c.Value = "N" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("T").Column).Locked = True
MsgBox "BETTER LUCK NEXT YEAR" & vbCrLf & "Now PAY Your dues!", vbInformation, "LOSER!!"
ElseIf c.Value = "Y" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("T").Column).Locked = False
MsgBox "YOU OWE $20 MORE DOLLARS!" & vbCrLf & "Good luck this week!", vbInformation, "PAY UP!"
End If
Next c
End If
End Sub

Public Sub My_Sub_D(ByVal Target As Range)
Dim R As Range, c As Range
Set R = Range("Z5:ZI50")
If Not Intersect(Target, R) Is Nothing Then
Me.Protect Password:="123456", userinterfaceonly:=True
For Each c In Intersect(Target, R)
If c.Value = "N" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("Z").Column).Locked = True
MsgBox "BETTER LUCK NEXT YEAR" & vbCrLf & "Now PAY Your dues!", vbInformation, "LOSER!!"
ElseIf c.Value = "Y" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("Z").Column).Locked = False
MsgBox "YOU OWE $25 MORE DOLLARS!" & vbCrLf & "Good luck this week!", vbInformation, "PAY UP!"
End If
Next c
End If
End Sub

Public Sub My_Sub_E(ByVal Target As Range)
Dim R As Range, c As Range
Set R = Range("AF5:AFI50")
If Not Intersect(Target, R) Is Nothing Then
Me.Protect Password:="123456", userinterfaceonly:=True
For Each c In Intersect(Target, R)
If c.Value = "N" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("AF").Column).Locked = True
MsgBox "BETTER LUCK NEXT YEAR" & vbCrLf & "Now PAY Your dues!", vbInformation, "LOSER!!"
ElseIf c.Value = "Y" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("AF").Column).Locked = False
MsgBox "YOU OWE $30 MORE DOLLARS!" & vbCrLf & "Good luck this week!", vbInformation, "PAY UP!"
End If
Next c
End If
End Sub


Public Sub My_Sub_F(ByVal Target As Range)
Dim R As Range, c As Range
Set R = Range("AL5:ALI50")
If Not Intersect(Target, R) Is Nothing Then
Me.Protect Password:="123456", userinterfaceonly:=True
For Each c In Intersect(Target, R)
If c.Value = "N" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("AL").Column).Locked = True
MsgBox "BETTER LUCK NEXT YEAR" & vbCrLf & "Now PAY Your dues!", vbInformation, "LOSER!!"
ElseIf c.Value = "Y" Then
c.Offset(0, 1).Resize(1, Columns("DO").Column - Columns("AL").Column).Locked = False
MsgBox "YOU OWE $30 MORE DOLLARS!" & vbCrLf & "Good luck this week!", vbInformation, "PAY UP!"
End If
Next c
End If
End Sub

 

[Kenneth Hobson]

Application.EnableEvents = False
'Do changes
Application.EnableEvents = True

 

[scockster]

I'm having trouble figuring out where to put the EnableEvents. Can you help with that please?

 

[Kenneth Hobson]

Before and after changes?

I typically do the false after an exit sub or near top of code. The true is near the end.

I would not put those inside a loop. Put false before the loop begins and true after loop ends.