Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Boforeclose event when cancel is pressed

1

Hello,

workbook_beforeclose event fires when the user press the Close button but if there are any changes it will ask him to save and he could press Cancel

The question is how I could make this event runs only when the user close the file completely or how I could know if he pressed Cancel.

Thanks.

## EDIT ##

After I searched the Internet, I found one approach by duplicate the save msg by new one, that know whether the user chosen Cancel or not, you could see it here

http://spreadsheetpage.com/index.php/tip/handling_the_workbook_beforeclose_event/

I will not make the question answered so I could receive another answers (if possible)

## 2nd EDIT ##

There is another way -it is what I'm using right now- by put the second code that should be fired when you click Cancel in an Worksheet_deactivate event so you could know if the file is open when the user change the sheets

(This helped me because I have a specific sheet is selected when you close the file and this sheet is unusable)

OR you could put that could in change event or a button

####

I found a solutin for my question but I'm trying to help anyone read this thread later.

I will post an answer to this question if there are no any other answers, and I will put these tries in it.

Answer
Discuss

Discussion

Can you just save the file from the workbook beforeclose event?

It depends on the file that you use this in, but, if the user is not allowed to change the workbook, you could close the workbook from this macro and set it not to save OR you could set it to automatically save the workbook and then close it.

If this is something that might work for you I will put some sample code in an answer and submit it.
don (rep: 1989) Jul 9, '16 at 12:49 pm
Add to Discussion

Answers

1
Selected Answer

Because I didn't find an actual answer, I'll post the possible solutions,

  • This answer is by Don (Thanks you), save the file from the workbook beforeclose event

    It depends on the file that you use this in, but, if the user is not allowed to change the workbook, you could close the workbook from this macro and set it not to save OR you could set it to automatically save the workbook and then close it.
  • duplicate the save msg by new one, that know whether the user chosen Cancel or not, you could see it here

    http://spreadsheetpage.com/index.php/tip/handling_the_workbook_beforeclose_event/ 
  • put the second code that should be fired when you click Cancel in a Worksheet_deactivate event so you could know if the file is open when the user changes the sheets.

    (This helped me because I have a specific sheet is selected when you close the file and everything in this sheet is protected -read only- )

Thanks for everyone tries to search in the Internet for that, even if he didn't discuss or answer.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login