Workbook_BeforeClose (Cancel As Boolean)

[rob_sheeds]

Hi, I have read all there is in here on the Workbook_BeforeClose and I cant get it to work. The jist is to protect all sheets and the workbook whenever a user closes the workbook.
Current code is (which is under ThisWorkbook). Any help would be excellent and thanks in advance.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call ProtectAllSheets

End Sub

Sub ProtectAllSheets()
'Protects ALL worksheets
For Each Shts In ThisWorkbook.Worksheets
Shts.Protect Password:="evo!@#"
Next
ActiveWorkbook.Protect Password:="evo!@#"
End Sub

 

[Michael M]

Works OK for me....is there an error or is it just not protecting / closing ??

 

[rob_sheeds]

HI Michael,
There is no error, it closes fine. I know something is happening as I save, a dialog appears, then I click the red X to close and am asked to save again, so something has changed and it is asking me to save the changes before close. But upon open the workbook is not protected. The macro to protect works fine too. Im stumped.

 

[Michael M]

What's in the Dialog Box ??

 

[ttdk1]

in the private workbook_beforeclose subroutine, put the following lines, in that order.

Call ProtectAllSheets
ThisWorkbook.Close SaveChanges:=True

 

[rob_sheeds]

it is just the normal SaveAs dialog box. I tried that code and nothing. Still doesnt protect anything.
Any way of working out if it is getting used?

 

[ttdk1]

works fine for me. when I put the call protectallsheets after the other command. workbook saves and closes but no protection. however, in the above order, workbook protect,saves, and closes just fine. no dialog box.

BUUUTTTTT...

if you are getting a dialog box asking you if you want to save.. that would mean that the code Thisworkbook.close save changes:=true is not working..

hmm hmm.. did you by chance have designer mode enabled when you close the workbook? if you do, turn off designer mode, then give it a try again

 

[Michael M]

Ok, dumb question, but is this a new workbook...ie, Book1...that hasn't been saved before ??

 

[rob_sheeds]

ok im confused...tried both having the SaveAs line before and after calling the other sub but doesnt work.
This is what I have. It is the 3rd module, and is in ThisWorkbook. My personal.xlsb is open too. Read somewhere that may be an issue? Im not familiar with debugging or break points, maybe that would help to see if it actually gets called?
Here is the code as it stands;

Private Sub Workbook_BeforeClose(Cancel As Boolean)


ThisWorkbook.Close SaveChanges:=True
Call ProtectAllSheets


End Sub



Sub ProtectAllSheets()
'Protects ALL worksheets
For Each Shts In ThisWorkbook.Worksheets
Shts.Protect Password:="evo!@#"
Next
ActiveWorkbook.Protect Password:="evo!@#"
End Sub

 

[rob_sheeds]

No this isnt a new workbook