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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,159
Office Version
  1. 365
Platform
  1. Windows
Code:
Application.EnableEvents = False
'Do changes
Application.EnableEvents = True
 

scockster

New Member
Joined
Feb 15, 2019
Messages
17
I'm having trouble figuring out where to put the EnableEvents. Can you help with that please?
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,159
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,457
Messages
5,624,853
Members
416,063
Latest member
chaulon199

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
Top