Hi,

I have a macro below that needs recoded as it's not working, I am looking
for the macro so when I press the save button it automatically protects all
the worksheets in the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ProtectAllSheets.protect Password:="ABCD"
End With
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub


Many thanks

hi
perhaps you are looking for the before save event not the before close event.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

regards
FSt1

"santaviga" wrote:

Hi,

I have a macro below that needs recoded as it's not working, I am looking
for the macro so when I press the save button it automatically protects all
the worksheets in the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ProtectAllSheets.protect Password:="ABCD"
End With
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub


Many thanks

Hi, i've replaced with the new line of code but still not functioning I think
rest of code might be wrong any ideas?

Regards

"FSt1" wrote:

hi
perhaps you are looking for the before save event not the before close event.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

regards
FSt1

"santaviga" wrote:

Hi,

I have a macro below that needs recoded as it's not working, I am looking
for the macro so when I press the save button it automatically protects all
the worksheets in the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ProtectAllSheets.protect Password:="ABCD"
End With
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub


Many thanks

Try the following code in the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Protect ' password:="your password"
Next WS
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"santaviga" wrote in message
...
Hi,

I have a macro below that needs recoded as it's not working, I am looking
for the macro so when I press the save button it automatically protects
all
the worksheets in the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ProtectAllSheets.protect Password:="ABCD"
End With
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub


Many thanks