+ Reply to Thread
Results 1 to 23 of 23

Adding auto-close code to existing?

  1. #1
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    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:

    Please Login or Register  to view this content.
    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!

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Adding auto-close code to existing?

    The code you show here does not close the file. It forces the file to be saved before it is closed, so this is not what you want to change.

    If you want it to close then add this to the end of the Submit button code:
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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???

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Adding auto-close code to existing?

    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

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Adding auto-close code to existing?

    nimrod, your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

  6. #6
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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...

  7. #7
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    Trying to place my Submit code again:

    Please Login or Register  to view this content.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Adding auto-close code to existing?

    If you want to 'close' a userform unload it.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  9. #9
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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!

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Adding auto-close code to existing?

    Where is the Submit button and its code located?

  11. #11
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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...

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Adding auto-close code to existing?

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  13. #13
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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...)

  14. #14
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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!

  15. #15
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

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

  16. #16
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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!

  17. #17
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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

    Please Login or Register  to view this content.
    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:

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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

  19. #19
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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!

  20. #20
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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!

  21. #21
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

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

  22. #22
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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!

  23. #23
    Registered User
    Join Date
    04-06-2016
    Location
    Tacoma, WA
    MS-Off Ver
    2010
    Posts
    21

    Re: Adding auto-close code to existing?

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] adding a value to existing code
    By bnwash in forum Excel General
    Replies: 16
    Last Post: 06-05-2015, 07:40 PM
  2. Adding to some existing VBA code
    By richard11153 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 03-10-2014, 08:31 PM
  3. [SOLVED] Adding an auto delete rows to yours already existing macro for emailing
    By msgtrainey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-19-2012, 06:20 AM
  4. Need help with auto close code
    By revlemmon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2012, 07:31 AM
  5. Replies: 2
    Last Post: 03-17-2011, 08:55 PM
  6. Help adding to existing code
    By Jrykiss in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-02-2010, 06:11 PM
  7. Adding Code to an Existing UserForm with VBA
    By JimmyA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-08-2010, 12:02 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1