PDA

View Full Version : [SOLVED] Message Box Still Appearing When Code Runs Correctly



Marhier
10-22-2017, 11:33 PM
Hello everyone.
I've made a form that relies on someone copying data from a separate location and then clicking on a button that generates and saves as a copy.
I've used an On Error code for when they haven't copied the data from the other location; I have it bring up a message box that says 'Copy data first!' and then exits the sub.

The issue I'm having is, when there isn't an error and the code completes correctly, the message box still appears.
Could someone let me know where I'm going wrong; how do I make it so that message only comes up when the error occurs, and not when the code completes correctly as well?


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

All help is greatly appreciated.
Thank you.
Regards
Martin

Marhier
10-22-2017, 11:57 PM
I've JUST realised what I did wrong!

I moved my Exit Sub above the Errhandler: code!

Derp!

MINCUS1308
10-23-2017, 08:36 AM
that's awesome!

always exit before handling your errors

If this problem is resolved can you please mark the thread as solved :)

MINCUS1308
10-23-2017, 08:37 AM
also, Welcome to the Forum!!

Logit
10-23-2017, 11:33 AM
.
Try commenting out the "On Error GoTo Errhandler" line and run the macro again.

I believe you have an error that hasn't been located yet ?

MINCUS1308
10-23-2017, 01:01 PM
The code just never exited before the error handling section. I believe Marhier found the error.
Either Way, the resolution is 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
Application.ScreenUpdating = True

Exit Sub '<<<<<<<<< This Exit Sub Was Missing From The Code

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

Marhier
10-23-2017, 10:58 PM
Marked as solved as requested.
Appreciate everyone's support, comments and warm welcome.

Regards
Martin