Run Protect Macro on Close

[samsilverman]

Excel 07. I have unprotect and protect macros that run when I click on two different buttons. Sometimes, the tech (end user) forgets to reprotect the workbook before closing it. How can I get the macro I already have (which protects 7 worksheets with different passwords) to run automatically upon closing?

 

[dave3009]

Use the workbook before close event to call the protect macro

 

[VoG]

Try like this in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call MyMacro
End Sub

 

[samsilverman]

Try like this in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call MyMacro
End Sub

So, does the name of the macro I want to run go in place of "MyMacro" in your code above? Because I can't find the name of my macro in the Macros List. When I look at the MEO sheet in VBA, the macro is there with the heading:
Private Sub CommandButton3_Click()

Help?

 

[VoG]

Instead of MyMacro try CommandButton3_Click

If that doesn't work, copy the code from CommandButton3_Click (without the first and last lines) and paste it into the WorkbookBeforeSave sub instead of the Call statement.

 

[samsilverman]

Instead of MyMacro try CommandButton3_Click

If that doesn't work, copy the code from CommandButton3_Click (without the first and last lines) and paste it into the WorkbookBeforeSave sub instead of the Call statement.

Thanks VoG. Works Great!! I took the

copy the code from CommandButton3_Click (without the first and last lines) and paste it into the WorkbookBeforeSave sub instead of the Call statement.