Consulting

Results 1 to 7 of 7

Thread: Message Box Still Appearing When Code Runs Correctly

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    27
    Location

    Message Box Still Appearing When Code Runs Correctly

    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

  2. #2
    VBAX Regular
    Joined
    Oct 2017
    Posts
    27
    Location
    I've JUST realised what I did wrong!

    I moved my Exit Sub above the Errhandler: code!

    Derp!

  3. #3
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    254
    that's awesome!

    always exit before handling your errors

    If this problem is resolved can you please mark the thread as solved
    - I HAVE NO IDEA WHAT I'M DOING

  4. #4
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    254
    also, Welcome to the Forum!!
    - I HAVE NO IDEA WHAT I'M DOING

  5. #5
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    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 ?

  6. #6
    VBAX Tutor MINCUS1308's Avatar
    Joined
    Jun 2014
    Location
    UNDER MY DESK
    Posts
    254
    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
    - I HAVE NO IDEA WHAT I'M DOING

  7. #7
    VBAX Regular
    Joined
    Oct 2017
    Posts
    27
    Location
    Marked as solved as requested.
    Appreciate everyone's support, comments and warm welcome.

    Regards
    Martin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •