Exit a macro and display a message when criteria not met

[Tony Miall]

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

 

[Sal Paradise]

Try putting this in your code somewhere. It'd help if you posted your code so we could see what you're doing.

With Worksheets("Sheet1")
    If .Cells("R23").Value <> "ORDER VERIFIED" Then
        MsgBox(.Cells("R23").Value & ": Check Specification")
    End If
End With

 

[Tony Miall]

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

 

[Sal Paradise]

Sorry, change it to:

With Worksheets("ORDER FORM")
    If .Range("R23").Value <> "ORDER VERIFIED" Then
        MsgBox (.Range("R23").Value & ": Check Specification")
    End If
End With

 

[Trebor76]

Here's my 2c worth:

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

 

[Tony Miall]

Thank you both.

Sal yours let the macro continue after OK was clicked.

Trebor's exited the macro and works perfectly.

Thanks again

 

[Trebor76]

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

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

Exit Sub

just above this line of code:

End If

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

HTH

Robert

 

[Tony Miall]

Of course.

Thanks again