Adding auto-close code to existing?

I've adapted a downloaded userform from contextures.com that automatically populates to a database worksheet. I'm new to coding, but was able to adjust/adapt code to my needs (with a little help from this forum!) Everything is now working fine, BUT with initial testing feedback, I need to add code to automatically CLOSEthe form after the user hits the Submit button (which also auto-opens Save As PDF, then auto-attaches PDF to a notification email, then auto-saves the Excel file), so that other users can access the form.

My auto-save code is in ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  'Save changes to workbook when closing, without prompting user.
  Me.Save
End Sub

Would I add to/change this code to achieve the auto-close function or would it be better to add it to the Submit button macro code? I've been searching the forums and tried a few things, but not getting the solution I need. Thanks in advance for any help/advice you can offer!

HI Jeff--thanks for the quick response! When I add the above code to my Submit button code, it seems to disrupt a few things...now I get a Save dialog instead of the auto-save function, and after manually saving, when I re-open the file, the fields in my userform have not cleared. All of this was functioning perfectly before; I'm wondering if the code needs to go somewhere else???

Public Sub submit()

' turn off prompts
Application.DisplayAlerts = False

ThisWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Save changes to workbook when closing, without prompting user.
Me.Save

' turn prompts back on
Application.DisplayAlerts = True

' place clear workbook code here


End Sub

Thanks, Nimrod, for your suggestion. I'm still having issues (getting compile errors) when I attempt to place the code at the end of my Submit button code (see existing, working code below). I've tried placing your suggested code before the code that clears the form, then I get an 'Expected End sub' indicating after the GoTo Quit: End Select line. When I add an End Sub there, I then get a different error: 'Invalid use of Me keyword', indicating the Me.Save code (when it is placed as indicated in Nimrod's suggested code; it currently lives in ThisWorkbook module).

I'm sure this is something relatively simple to someone who knows coding...can someone indicate the correct placement to add an auto-close function to this code that doesn't disrupt my auto-save AND the clear contents function for the userform fields???

Any assistance is much appreciated....


***CAN'T Reply with placed code in message?! Keep getting 'Sucuri Firewall' error msg that HTML code is not allowed...WTF?!!!

Help, please...

Trying to place my Submit code again:

Sub UpdateLogWorksheet()

Dim Msg As String, Ans As Variant
     
    Msg = "Did you print a copy of your Request Form for your records?"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myCopy As Range
    Dim myTest As Range
    
    Dim lRsp As Long
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim StrBody As String
    
    Dim FileName As String

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("RequestData")
    oCol = 2 'order info is pasted on data sheet, starting in this column
    
      
    'cells to copy from Input sheet - some contain formulas
    Set myCopy = inputWks.Range("OrderEntry")
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With historyWks
        nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
          'mandatory fields are tested in hidden column
        Set myTest = myCopy.Offset(0, 2)

        If Application.Count(myTest) > 0 Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With

    With historyWks
          'copy the order data and paste onto data sheet
        myCopy.Copy
        .Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Application.CutCopyMode = False
    End With
    
    
'Call the function with the correct arguments
    'Tip: You can also use Sheets("YourSheetName") instead of ActiveSheet in the code(sheet not have to be active then)

    FileName = RDB_Create_PDF(Source:=ActiveSheet, _
                              FixedFilePathName:="", _
                              OverwriteIfFileExist:=True, _
                              OpenPDFAfterPublish:=False)

    'For a fixed file name use this in the FixedFilePathName argument
    'FixedFilePathName:="C:\Users\Ron\Test\YourPdfFile.pdf"


    If FileName <> "" Then
RDB_Mail_PDF_Outlook FileNamePDF:=FileName, _
                             StrTo:="email@company.com", _
                             StrCC:="email@company.com", _
                             StrBCC:="", _
                             StrSubject:="New Photography Request Form", _
                             Signature:=True, _
                             Send:=False, _
                            StrBody:="A new Photography Request Form has been entered; please see attached PDF file."
    Else
        MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
               "Microsoft Add-in is not installed" & vbNewLine & _
               "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
               "The path to Save the file in arg 2 is not correct" & vbNewLine & _
               "You didn't want to overwrite the existing PDF if it exist"
    End If

    Set OutMail = Nothing
    Set OutApp = Nothing
    
     Case vbNo
GoTo Quit:
    End Select
    
     'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With myCopy.Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
     
Quit:

End Sub

Thanks, Norie. When I add the Unload Me code at the end of my Submit button code, I get a compile error: Invalid use of Me keyword.
I'm adding it just before the final End Sub, since that seems like the logical place to put it...? I also tried placing it at the end of the auto-save code from the ThisWorkbook module (see my first post), but it had no effect there. I know there has to be a simple answer to this, but I just am not code-knowledgable enough to figure it out!

Hi Norie--I'm not sure how to answer your question (and it makes me feel like a doofus!) This form and its worksheets were downloaded from contextures.com, and I just tweaked and modified for my purposes. The Submit button is a macro button on my form, and the code lives in a modData module--does this make sense or are you looking for different info??? The code attached to the Submit button is above in this thread...

Sorry, I was out of the office at the end of last week--here is a copy of the workbook. Any help to finalize this auto-close issue, while preserving all existing functionality is MUCH appreciated!!! (I think I have the attachment uploaded...)

Bumping this to see if anyone can help now that I've uploaded a copy of the workbook. Just need to add an auto-close function that won't interfere with auto-save and clear contents current functionality. Thanks in advance for any assistance!

Hello-bumping again to see if anybody can help??? Thanks!

Hi everyone--sorry to keep bumping, but this is the LAST piece of the puzzle, in order to release this form for intracompany use! Does anyone know where to add an auto-close code that won't interfere with my current auto-save and clear contents??? Many thanks for any assistance!

I've done some more research and testing on my own (since I haven't gotten any new replies lately!) I thought I had it figured out with the addition of

ActiveWorkbook.Save
ActiveWorkbook.Close

at the end of my Submit button code, BUT although the form now still auto-saves and then does autoclose, when I re-open it the contents of the userform fields have not cleared! I thought that if I placed the additional code AFTER the ClearContents code, everything would work! Does anyone have any ideas??? The code for my Submit button is below, with the additional Save and Close code added:

Sub UpdateLogWorksheet()

Dim Msg As String, Ans As Variant
     
    Msg = "Did you print a copy of your Request Form for your records?"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myCopy As Range
    Dim myTest As Range
    
    Dim lRsp As Long
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim StrBody As String
    
    Dim FileName As String

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("RequestData")
    oCol = 2 'order info is pasted on data sheet, starting in this column
    
      
    'cells to copy from Input sheet - some contain formulas
    Set myCopy = inputWks.Range("OrderEntry")
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With historyWks
        nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
          'mandatory fields are tested in hidden column
        Set myTest = myCopy.Offset(0, 2)

        If Application.Count(myTest) > 0 Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With

    With historyWks
          'copy the order data and paste onto data sheet
        myCopy.Copy
        .Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Application.CutCopyMode = False
    End With
    
    
'Call the function with the correct arguments
    'Tip: You can also use Sheets("YourSheetName") instead of ActiveSheet in the code(sheet not have to be active then)

    FileName = RDB_Create_PDF(Source:=ActiveSheet, _
                              FixedFilePathName:="", _
                              OverwriteIfFileExist:=True, _
                              OpenPDFAfterPublish:=False)

    'For a fixed file name use this in the FixedFilePathName argument
    'FixedFilePathName:="C:\Users\Ron\Test\YourPdfFile.pdf"


    If FileName < > "" Then
RDB_Mail_PDF_Outlook FileNamePDF:=FileName, _
                             StrTo:="email@company.com", _
                             StrCC:="email@company.com", _
                             StrBCC:="", _
                             StrSubject:="New Photography Request Form", _
                             Signature:=True, _
                             Send:=False, _
                            StrBody:="A new Photography Request Form has been entered; please see attached PDF file."
    Else
        MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
               "Microsoft Add-in is not installed" & vbNewLine & _
               "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
               "The path to Save the file in arg 2 is not correct" & vbNewLine & _
               "You didn't want to overwrite the existing PDF if it exist"
    End If

    Set OutMail = Nothing
    Set OutApp = Nothing
     
     Case vbNo
GoTo Quit:
    End Select
     
Quit:

'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With myCopy.Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

Hello--hope everyone had a great Fourth! And I'm REALLY hoping that someone has a simple solution as to why my ClearContents isn't working now with the addition of the above auto-close and auto-save code??? I haven't got any new answers or suggestions lately...I know somebody must have a great idea...help, please?! Thx

Bump--no response...does anyone have ANY suggestions? I've really hit the wall here; it's frustrating to have a completed, functioning form, but be unable to add an auto-close function without messing up other functionality! Would really appreciate some feedback--thanks!

Cross-posted to mrexcel.com: http://www.mrexcel.com/forum/excel-q...ml#post4576382

Any/all suggestions welcome, so I can finish this form--thanks!

Begging for some ideas to help me with this? Please???

Hi--I really need to add an auto-close function to my workbook/form; can anyone help??? Anyone? Help? Please??? I have exhausted my limited coding knowledge... Thanks!

Another week, another attempt to find the answer to my dilemma...I've spent half the day today researching the web and testing a number of suggested codes, placed in ThisWorkbook, that are supposed to automatically save, then close the workbook, either immediately or after a period of inactivity. I keep having the same problems with all these codes: either it does appear to save and close, but when I re-open the workbook, the fields of my data entry form have not cleared (this function works in the uploaded .xls file back on page one), OR I get a Save dialog (which I don't want!), then after I close and re-open, again no clearing of contents. I am at my wit's end to figure this out, and ready to just release it for our company use and people will just have to remember to close it on their own!!! Can ANYBODY help me with this, please, please, pretty please with sugar on top??? Thank you!