On Error - Message Box - End Sub

M

Marhier

Board Regular
Joined
Feb 21, 2017
Messages
128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Afternoon all.
Got a slight problem I'd appreciate some help with.

The form I've made relies on someone copying data from a separate location and then clicking on a button that generates and saves as a copy.
Where I'm struggling is figuring out where to put the OnError code when they haven't copied the data from the other location.

I just would like it so if there is nothing copied, it brings up a message box that says 'Copy data first!' and doesn't do anything else.

I've tried the following, but it still continues the code and messes up:

On Error GoTo Errormessage
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Exit Sub
Errormessage:
MsgBox "Copy data first!"

Here is the code without:

Sub Create()
If MsgBox("An Excel copy will be generated and you'll be notified to save.", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("DMSL").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1").Select
Sheets("DMSL").Select
Sheets("DMSL").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("13:13").Select
ActiveSheet.Range("$A$12:$AC$10000").AutoFilter Field:=1, Criteria1:="<>"
Range("U3:V4").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[10]C[3]:R[9997]C[3])"
Range("A1").Select
Application.Dialogs(xlDialogSaveAs).Show
MsgBox "Copy saved. The copy will now close." _
& vbCrLf _
& myFile
ActiveWorkbook.Close
Range("A1").Select
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Cells.Select
Selection.ClearContents
Range("A1").Select
Sheets("Home").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1:U4").Select
Application.ScreenUpdating = True
End Sub
Click to expand...

Really appreciate any support on this.
Thank you.
Regards
Martin
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi,
untested but try following & see if does what you want:

Code: 
Sub Create()


'check if clipboard has data
    If Application.ClipboardFormats(1) <> -1 Then
        response = MsgBox("An Excel copy will be generated and you'll be notified to save.", vbYesNo, "Copy Data")
            If response = vbNo Then Exit Sub
    
        'rest of your code
    
    Else
'inform user
        MsgBox "Copy data first!", 48, "ClipBoard Empty"
    End If


End Sub


Dave
 
Upvote 0
Morning Dave, and thank you for coming back to me.
So I gave it a go this morning and a few things happened.

1) The first time I tested it, it brought up the message saying "An Excel copy will be generated...", but when I clicked 'Yes', it brought up a Run-time error '1004': PasteSpecial method of Range class field.
When I clicked to debug, the following part of the code was highlighted:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


2) Just to make sure the rest of the code still worked, I tested it by copying data from the other location first and then hit my 'Create' button... Fortunately it still worked.

3) I then closed the sheet where I grab the information from, went back to my form and clicked the 'Create' button again and the code you suggested worked!
I got the message box titled 'ClipBoard Empty' with the text saying 'Copy data fist!

4) I thought it might be a glitch, so saved my worksheet, closed it, re-opened it, clicked the create button (without having copied any data from another sheet) and it gave me the Run-time error '1004' again.


My code is laid out as follows:

Sub Create()
Application.ScreenUpdating = False
'check if clipboard has data
If Application.ClipboardFormats(1) <> -1 Then
response = MsgBox("An Excel copy will be generated and you'll be notified to save.", vbYesNo, "Copy Data")
If response = vbNo Then Exit Sub

Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("DMSL").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1").Select
Sheets("DMSL").Select
Sheets("DMSL").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("13:13").Select
ActiveSheet.Range("$A$12:$AC$10000").AutoFilter Field:=1, Criteria1:="<>"
Range("U3:V4").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[10]C[3]:R[9997]C[3])"
Range("A1").Select
Application.Dialogs(xlDialogSaveAs).Show
MsgBox "Copy saved. The copy will now close." _
& vbCrLf _
& myFile
ActiveWorkbook.Close
Range("A1").Select
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Cells.Select
Selection.ClearContents
Range("A1").Select
Sheets("Home").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1:U4").Select

Else
'inform user
MsgBox "Copy data first!", 48, "ClipBoard Empty"
End If
Application.ScreenUpdating = True
End Sub
Click to expand...

Again, I appreciate any support.
Thank you.
Regards
Martin
 
Last edited:
Upvote 0
Ok, I on my way to solving this, but there's one slight problem I'm having.
I've gone back to it bringing up an message On Error, rather than it looking for what's in the clip board.

The code works perfectly for when there isn't anything copied...

The issue I'm having now is - when information is copied and the code completes correctly, at the end it's still giving me the message box to 'Copy the data first!'

How do I make it so that message only comes up when the error occurs, and not as well as when the code completes correctly?

My code is now as follows:

Sub Create()
Application.ScreenUpdating = False

On Error GoTo Errhandler

If MsgBox("An Excel copy will be generated and you'll be notified to save.", vbYesNo) = vbNo Then Exit Sub
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("DMSL").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1").Select
Sheets("DMSL").Select
Sheets("DMSL").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("13:13").Select
ActiveSheet.Range("$A$12:$AC$10000").AutoFilter Field:=1, Criteria1:="<>"
Range("U3:V4").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[10]C[3]:R[9997]C[3])"
Range("A1").Select
Application.Dialogs(xlDialogSaveAs).Show
MsgBox "Copy saved. The copy will now close." _
& vbCrLf _
& myFile
ActiveWorkbook.Close
Range("A1").Select
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Cells.Select
Selection.ClearContents
Range("A1").Select
Sheets("Home").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1:U4").Select


Errhandler:
MsgBox "Copy data first!"
Sheets("Home").Visible = xlSheetVisible
Sheets("Nexus Report").Visible = xlSheetVeryHidden
Exit Sub


Application.ScreenUpdating = True
End Sub
Click to expand...


Thank you.
Regards
Martin
 
Last edited:
Upvote 0
Apologies for bumping this, but am just after advice on how I make the 'Copy data first!' message box stop appearing when the code runs correctly.

Thank you.
Regards
Martin
 
Upvote 0
Try this:
Code: 
Sub Create()
Application.ScreenUpdating = False
On Error GoTo Errhandler
If MsgBox("An Excel copy will be generated and you'll be notified to save.", vbYesNo) = vbNo Then Exit Sub
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("DMSL").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1").Select
Sheets("DMSL").Select
Sheets("DMSL").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("13:13").Select
ActiveSheet.Range("$A$12:$AC$10000").AutoFilter Field:=1, Criteria1:="<>"
Range("U3:V4").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[10]C[3]:R[9997]C[3])"
Range("A1").Select
Application.Dialogs(xlDialogSaveAs).Show
MsgBox "Copy saved. The copy will now close." _
& vbCrLf _
& myFile
ActiveWorkbook.Close
Range("A1").Select
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Cells.Select
Selection.ClearContents
Range("A1").Select
Sheets("Home").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1:U4").Select
Sheets("Home").Visible = xlSheetVisible
Sheets("Nexus Report").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
Exit Sub
Errhandler:
MsgBox "Copy data first!"
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Legend!
I see what you've done... Just moved the exit sub etc earlier!

I had to move my other code to make certain sheets visible back as when the code fails it hides the main sheet.

Thanks a lot, I really appreciate it!

Regards
Martin
 
Last edited:
Upvote 0
I moved the Error handler after the Exit sub. And added the extra Screen up date
Marhier said:
Legend!
I see what you've done... Just added the ScreenUpdating = True earlier!

I had to move my other code to make certain sheets visible back as when the code fails it hides the main sheet.

Thanks a lot, I really appreciate it!

Regards
Martin
Click to expand...
 
Upvote 0
Note to self... Always Exit Sub before handling errors!

Thanks for your help!

Regards
Martin
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
152
MikeVol
MikeVol
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
280
willow1985
willow1985
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
211
kbk
K
brent3162
  • Solved
VBA help with increasing row before macro runs again.
Replies
3
Views
363
jolivanes
jolivanes
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
191
jolivanes
jolivanes

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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