On Error - would like the code to stop and a message to appear

[gheyman]

This does and does not work.

I am using this for when someone copies somethig from another workbook. I want them to Paste Values so they dont screw up the tables formatting.

if they copied something from another workbook this does paste the value in - but it also presents the Message.

If they didnt copy something and run the code (the code is on a button on the sheet) then the message pops up like expected and no error to stop

How do I fix this so that the message only appears if the code errors?

Sub PasteValues123()

'

        On Error GoTo ErrMsg
        
    Sheets("Step 2").Range("C16").Select
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
        False, NoHTMLFormatting:=True
        
        Application.CutCopyMode = False


ErrMsg:
MsgBox "There isnt anything to Paste"
        
End Sub

thanks for the help

 

[JGordon11]

Add

Exit Sub

a line above ErrMsg:

 

[Micron]

a more generic way to handle errors of most types is

On Error GoTo errHandler

'some code here

exitHere:
'do any cleanup, such as
'closing recordsets,
'setting all declared and Set objects to Nothing,
'restoring app properties, e.g. Warnings,
'etc.
Exit Sub

errHandler:
'any error handling, e.g. If this Err.Number do this, else, and so on, or just
Msgbox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[tonyyy]

And another approach...

ErrMsg:
If Err.Number <> 0 Then MsgBox "There isnt anything to Paste"

Cheers,

Tony

 

[gheyman]

Can you have an On Error within an On Error?

Sub PasteValues123()


'*********************************************************
'Add Formula, Calculate and then Paste Values for Formulas
'*********************************************************


         Range("C13").Select
            ActiveCell.FormulaR1C1 = _
                "=CONCATENATE(""Count: "", SUBTOTAL(103,Table10[Enter Your Part Numbers]))"
           


        Calculate
    
        Range("C13").Select
            Selection.Copy
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                            Application.CutCopyMode = False
        
      
        
        Range("C16").Select
        
        
'**********************************
'Paste Special the selections
'**********************************


        On Error GoTo ErrMsg2
        
            Sheets("Step 2").Range("C16").Select
            ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
                False, NoHTMLFormatting:=True
        
                Application.CutCopyMode = False
        
        Exit Sub
        
                
ErrMsg2:

         On Error GoTo ErrMsg
        
            Sheets("Step 2").Range("C16").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
                
                Application.CutCopyMode = False
                
            Exit Sub
         

ErrMsg:

  
    MsgBox "Paste Special Values Completed"
        
End Sub

 

[Micron]

Can you have an On Error within an On Error?

Only in the sense that if a subsequent one is processed it will direct the flow accordingly thereafter. There is no going back any previous handler without explicitly writing and executing it again.

 

[JGordon11]

Can you have an On Error within an On Error?

Answer is as Micron said.

Note that On Error Goto 0 will turn off error trapping. So good practice is to wrap your line of code that has a predictable error like this:

On Error Goto ErrHandler
... code that may trigger an error ...
On Error Goto 0

That way if there are errors from subsequent parts of the code, they will still cause the code execution to break and allow you to debug it.

On some occasions you may be ok with the code just ignoring the line that caused an error and continue running as if that line had never been executed. In that case you use:

On Error Resume Next
... code that may trigger an error ...
On Error Goto 0