Exit a macro and display a message when criteria not met

Tony Miall

Active Member
Joined
Oct 16, 2007
Messages
304
Hi all,

I have a macro that when run sends part of a sheet to a new workbook.

There is cell (R23) in the original work book with a formula that displays various statuses based on user input ie "ORDER VERIFIED", "PRICING ERROR", "OVER SPEC QUANTITY" etc.

What I want to happen is exit the macro and display a message to the user, something like "THIS ORDER IS NOT VERIFIED, CHECK SPECIFICATION" if the status in R23 does not equal "ORDER VERIFIED".

I have searched other posts but can't find what I'm after.

Appreciate any help.

Thanks, Tony
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try putting this in your code somewhere. It'd help if you posted your code so we could see what you're doing.
Code:
With Worksheets("Sheet1")
    If .Cells("R23").Value <> "ORDER VERIFIED" Then
        MsgBox(.Cells("R23").Value & ": Check Specification")
    End If
End With
 
Upvote 0
Hi Sal, thanks for the quick reply.

I put your code in at the start of the macro and changed to the relevant worksheet name.

I get a run time error '13':

Type mis match

with this line of code highlighted in debug mode:

If .Cells("R23").Value <> "ORDER VERIFIED" Then

The whole code is below,

Thanks


Sub FINALISETOFACTORY()
'
' FINALISETOFACTORY Macro
' Macro recorded 24/11/2008 by tmiall
'

'
With Worksheets("ORDER FORM")
If .Cells("R23").Value <> "ORDER VERIFIED" Then
MsgBox (.Cells("R23").Value & ": Check Specification")
End If
End With
Application.ScreenUpdating = False
Range("A20:X73").COPY
Range("A600").Select
Windows("FACTORY ORDER TEMPLATE.xls").Activate
Sheets("Factory Order").Select
Range("A1:B1").Select
ActiveSheet.PASTE
Range("A1:X54").Select
Application.CutCopyMode = False
Selection.COPY
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("TCFDATA.xls").Activate
Sheets("ORDER FORM").Select
Range("A600:IV600").COPY
Range("D24").Select
Windows("FACTORY ORDER TEMPLATE.xls").Activate
Sheets("Factory Order").Select
Range("A102").PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Protect Password:="snip"
Range("D5").Select
ActiveSheet.Calculate
' Copy the data (panels, gates and posts) to work sheet area for compressing
Sheets("VBA Work Sheet").Activate
ActiveSheet.Calculate
Columns("a:i").Clear
Range("j1:k166").COPY
Range("a1").PasteSpecial PASTE:=xlPasteValues
' Compress the data (panels, gates and posts)
Let col = 0
' Loop for each list of (panels, gates or posts)
Do Until col = 9
Range("a1").Offset(0, col).Select
' Loop thru each list of (panels, gates or posts) deleting Zero lines
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 0 Then
ActiveCell.Range("A1:C1").Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Loop
Let col = col + 3
Loop
' Move compressed data to linked area in work sheet for order makeup
Range("A1:I11").COPY Range("r1")
Range("A1:B80").COPY
Sheets("Factory Order").Activate
Range("z12").PasteSpecial PASTE:=xlPasteValues
Range("Z12").Select
ActiveSheet.Calculate
Application.ScreenUpdating = True


End Sub
 
Upvote 0
Sorry, change it to:
Rich (BB code):
With Worksheets("ORDER FORM")
    If .Range("R23").Value <> "ORDER VERIFIED" Then
        MsgBox (.Range("R23").Value & ": Check Specification")
    End If
End With
 
Upvote 0
Here's my 2c worth:

Code:
Sub Macro1()

    With Worksheets("ORDER FORM")
        If StrConv(.Range("R23").Value, vbUpperCase) <> "ORDER VERIFIED" Then
            MsgBox "THIS ORDER IS NOT VERIFIED, CHECK SPECIFICATION" & vbNewLine & "THE PROCESS HAS BEEN TERMINATED", vbCritical, "Order Processor Editor"
            Exit Sub
        End If
    End With

End Sub

HTH

Robert
 
Upvote 0
Thank you both.

Sal yours let the macro continue after OK was clicked.

Trebor's exited the macro and works perfectly.

Thanks again
 
Upvote 0
Thanks for letting us know and you're welcome :wink:

Just to let you know that if you add this line of code:

Code:
Exit Sub

just above this line of code:

Code:
End If

to Sal's code it will work fine as well.

HTH

Robert
 
Upvote 0

Forum statistics

Threads
1,218,617
Messages
6,143,498
Members
450,491
Latest member
Ccruz444

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