If macro with Msg

APPPRO

Active Member
Joined
Aug 6, 2002
Messages
256
I have the following Macro that tells the user that they have skipped a line and therefore they have skipped a order. I have 10 entries each with a PullDown menu to access the info and have attached this macro to each one. It works, but when activated it finds All the skipped lines and NOT just the one activated. How can I stop that?

Sub SkipAnFO()
Set Entry1 = Range("f11")
Set Entry2 = Range("f12")
Set Entry3 = Range("f13")
Set Entry4 = Range("f14")
Set Entry5 = Range("f15")
Set Entry6 = Range("f16")
Set Entry7 = Range("f17")
Set Entry8 = Range("f18")
Set Entry9 = Range("f19")
Set Entry10 = Range("f20")

If Entry1 < 16 And Entry2 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
If Entry2 < 16 And Entry3 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
If Entry3 < 16 And Entry4 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
If Entry4 < 16 And Entry5 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
If Entry5 < 16 And Entry6 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
If Entry6 < 16 And Entry7 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
If Entry7 < 16 And Entry8 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
If Entry8 < 16 And Entry9 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
If Entry9 < 16 And Entry10 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no."

End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You need to use an If..Then..ElseIf construct, like this:

Code:
If Entry1 < 16 And Entry2 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no." 
ElseIf Entry2 < 16 And Entry3 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no." 
ElseIf Entry3 < 16 And Entry4 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no." 
ElseIf Entry4 < 16 And Entry5 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no." 
ElseIf Entry5 < 16 And Entry6 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no." 
ElseIf Entry6 < 16 And Entry7 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no." 
ElseIf Entry7 < 16 And Entry8 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no." 
ElseIf Entry8 < 16 And Entry9 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no." 
ElseIf Entry9 < 16 And Entry10 > 15 Then MsgBox "STOP! You've skipped a line. Skipping lines is a no no." 
End If

In your code each of your If statements is evaluated. With the If..Then..ElseIf construct execution ends after a true condition is found.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry I did not notice that there was no line feed after Then.

The code should look like this:

Code:
If Entry1 < 16 And Entry2 > 15 Then
    MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
ElseIf Entry2 < 16 And Entry3 > 15 Then
    MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
ElseIf Entry3 < 16 And Entry4 > 15 Then
    MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
ElseIf Entry4 < 16 And Entry5 > 15 Then
    MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
ElseIf Entry5 < 16 And Entry6 > 15 Then
    MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
ElseIf Entry6 < 16 And Entry7 > 15 Then
    MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
ElseIf Entry7 < 16 And Entry8 > 15 Then
    MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
ElseIf Entry8 < 16 And Entry9 > 15 Then
    MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
ElseIf Entry9 < 16 And Entry10 > 15 Then
    MsgBox "STOP! You've skipped a line. Skipping lines is a no no."
End If
 

APPPRO

Active Member
Joined
Aug 6, 2002
Messages
256
Does the same thing. Only way I can get it to work is to put each If into a separate Sub and then assign each Sub to a Pulldown.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The message box only appeared once when I tested it.

Could you post a sample of the data in F11:F20? And what is a PullDown menu?
 

Forum statistics

Threads
1,144,510
Messages
5,724,777
Members
422,576
Latest member
kayth891

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