+ Reply to Thread
Results 1 to 5 of 5

Disabling the X button stops Excel from saving and closing

  1. #1
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 365
    Posts
    50

    Disabling the X button stops Excel from saving and closing

    Hi Excel experts, I need your help.
    I created an EXIT button for user to close the worksheet (and also to save it) instead of using the X button.
    However, it seems to prevent my EXIT button from saving the worksheet and exiting Excel.
    I've attached a sample file to show what happened.
    Please help me to solve this puzzling problem.

    Thank you.
    Chan K.S.
    Attached Files Attached Files

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Disabling the X button stops Excel from saving and closing

    .
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 365
    Posts
    50

    Re: Disabling the X button stops Excel from saving and closing

    Thank you, Logit for the quick reply.
    When I followed your suggestion to change "Case Is = vbYes" to "Case vbYes", it still didn't work.
    I added "Public CloseMode As Boolean" to the coding for the EXIT macro and now I can close the worksheet and exit Excel by clicking YES option.
    However, when I included an Option for CANCEL, clicking on it or the YES option will NOT close the worksheet nor exit Excel.
    Any suggestion? Or should I forget about including the CANCEL option. It seems that 'something' is preventing the worksheet from closing without being saved (which I wanted for this option).
    Thanks again.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Disabling the X button stops Excel from saving and closing

    .
    Here is the revised code and a sample workbook :

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 365
    Posts
    50

    Re: Disabling the X button stops Excel from saving and closing

    Thanks, Logit. It works for both YES and NO options.
    But I also want user not to use the X button.
    In the ThisWorkbook, I have the following code and Excel doesn't close on both YES and NO options!

    Please Login or Register  to view this content.
    I wonder why the code in ThisWorkbook is causing this! Perhaps it is exit is either by using my own EXIT button or using the X button. Cannot have both!

+ 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. Saving personal workbook before closing excel
    By Thor Almighty in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2015, 06:27 AM
  2. Creating new workbook, saving as csv and then closing - but closing always fails.
    By ella626 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-28-2014, 10:05 PM
  3. disabling the Excel office button
    By dghall in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-13-2012, 12:05 PM
  4. [SOLVED] Opening, closing, saving in different Excel versions
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-01-2012, 09:43 AM
  5. disabling macros when closing WB
    By orbatrin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-06-2010, 09:04 PM
  6. [SOLVED] Closing Excel without saving
    By Abs in forum Excel General
    Replies: 9
    Last Post: 12-07-2005, 11:15 AM
  7. Saving and Closing multiple files and Excel program
    By Roger in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-18-2005, 11:05 PM

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