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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,223
Office Version
  1. 365
Platform
  1. Windows
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?

Code:
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

JGordon11

Well-known Member
Joined
Jan 18, 2021
Messages
604
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Add

VBA Code:
Exit Sub

a line above ErrMsg:
 
Solution

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,159
Office Version
  1. 365
Platform
  1. Windows
a more generic way to handle errors of most types is

VBA Code:
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

Well-known Member
Joined
Jun 24, 2015
Messages
1,784
Office Version
  1. 2010
Platform
  1. Windows
And another approach...

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

Cheers,

Tony
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,223
Office Version
  1. 365
Platform
  1. Windows
Can you have an On Error within an On Error?

Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
4,159
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 18, 2021
Messages
604
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,181,212
Messages
5,928,719
Members
436,625
Latest member
agholson81

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