Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Before close event works, however need a way to close form when done

    I have an Excel workbook where I have a beforeclose event and this event looks for certain fields to be filled in and then when the user tries to close if they are not all filled in it will highlight what they missed. Then they can close when done and it will automatically saveas a file name and to a certain area. They can do this numerous times because they often have 5-10 audits to fill out and can do it by pulling up the workbook one time and doing it all without a final close of the form.

    The problem is, when they are done and want to close all together, it will continue to prompt them to fill in the certain fields. I do not want them to have to fill in the fields and then save and close where it would save the last bit of data they entered. It should always be a blank template to begin with, however it is a .xlsm. I just want to program either in the beforeclose event a way for them to finally get out when done or in a beforesave that if they are complete done they can click on the blue disk (SAVE) and it will shutdown the form. I cannot program the event I have in the beforeclose in the save because that sends everything into this crazy loop.

    Here is my code:

    Code:
    
    Option Explicit
    
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
        Dim Start As Boolean
        Dim Rng1 As Range
        Dim Prompt As String, RngStr As String
        Dim Cell As Range
        Dim wbNewPath As String
    
        Sheets("Form").Unprotect Password:="audit"
        Set Rng1 = Sheets("Form").Range("B4, B7, B8, B9, B12, B74, G9, A16, B10, D10, E74, E77")
         'message is returned if there are blanks or no value in required fields
        Prompt = "Please make sure are highlighted fields are filled in." & vbCrLf & _
        "The following cells are incomplete and have been highlighted yellow:" _
        & vbCrLf & vbCrLf
        Start = True
         'highlights the blank cells
        For Each Cell In Rng1
            If Cell.Value = vbNullString Or Cell.Value = 0 Or Cell.Value <= 0 Then
                Cell.Interior.ColorIndex = 6 ' color yellow
                If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf
                Start = False
                RngStr = RngStr & Cell.Address(False, False) & ", "
            Else
                Cell.Interior.ColorIndex = 0 '** no color
            End If
        Next
        Sheets("Form").Protect Password:="audit", _
        UserInterFaceOnly:=True
        If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
        If RngStr <> "" Then
           MsgBox Prompt & RngStr, vbCritical, "Incomplete Data"
           Cancel = True
       Else
            'saves the changes before closing
           Application.DisplayAlerts = False
           ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ$("username") & _
           "\Documents\Audits\" & Range("B9").Text & Chr(32) & Range("B7").Text & _
           Chr(32) & Format(Range("B10").Value, "MM-DD-YYYY") & ".xlsx", _
           FileFormat:=xlOpenXMLWorkbook, CreateBackup:=True
           Application.DisplayAlerts = True
    End If
    
    
        Set Rng1 = Nothing
         ActiveSheet.Protect
    
    End Sub

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,826
    Thanks
    185
    Thanked 704 Times in 642 Posts
    Tina,

    Welcome to the Lounge as a New Poster!

    This should do what you want.
    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
     
     If vbYes = MsgBox("Do you want to exit now?", _
                   vbYesNo + vbInformation, _
                   "Exit Selection:") Then
       Cancel = False
     Else
       Cancel = True   '*** Exit Excel NOW - Remove this is for testing your code handles this. ***
    '   *** Your Existing Code Here ***
     End If
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

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