BeforeClose not running

TheMadTree

New Member
Joined
Mar 23, 2015
Messages
25
Good afternoon All,

My first post to MrExcel B)

I want a macro that password protects all sheets in the workbook called 'Potato' when the workbook is closed.
For some reason it doesn't work and I can't figure our why :s

Can anyone help me?

This is what I've written:

Option Explicit
Sub Protectsheets_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Dim pwd As String
pwd = Orange30
For Each ws In Workbooks("Potato.xlsm").Worksheets
ws.Protect Password:=pwd, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True
Next ws
If Me.Saved = False Then Cancel = True
End Sub
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

The event needs to read:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

And the code needs to go in the ThisWorkbook module.

HTH,
 

Watch MrExcel Video

Forum statistics

Threads
1,099,780
Messages
5,470,726
Members
406,719
Latest member
ensbana

This Week's Hot Topics

Top