Restricting close workbook until Saved in excel macro

[Scofield24]

Hi All,

I have created a workbook with multiple mandatory fields within it. What i wish is that all these fields must be filled before the workbook can be closed. For this i've tried 2 cases:-

Case 1: Using BeforeClose event in Macro

Problem with this is that user can leave the mandatory field blank and save it. Although he wont be able to close, but if he copies this saved workbook (by going to its location) to some other location(say) , he can use that copy as the original one.

Now you would think, why wouold he do that. Answer is i dont want to leave any loophole in this.


Case 2: Using BeforeSave event

Problem with this is that when a i try to close the sheet with some mandatory field blank, it will prompt me "Do you need to save the changes,etc" . If i click Yes, it will prompt (created by me) to fill the mandatory field , and then close. And the field would be blank, next time we open it.


So, How can i restrict the close until all the mandatory fields are filled and saved??


Appreciate your help.

Thanks