Message box is repeating with multiple worksheet changes

scockster

New Member
Joined
Feb 15, 2019
Messages
17
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Code:
Application.EnableEvents = False
'Do changes
Application.EnableEvents = True
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top