Calgarychris,

I wonder if it's because you are using an Excel keyword, "Protect", as a
module name? You might try renaming, in any event it's recommended not to
name variables, etc. with Excel keywords.

hth,

Doug

"Calgarychris" wrote in message
...
Hi,

I've scanned the Q&As for this but I can't seem to get the beforesave
event
to work. All I want to do is, just before saving, a macro would protect
each
sheet in the spreadsheet. I can run that macro easily enough on its own,
but
when I call that sub from the beforesub it doesn't seem to fire...it
doesn't
even allow me to step through it.

Obviously I'm very very green at all this...I checked my
application.enableevents was true (I'm not even sure what that does!),
I've
checked that it's located in the Thisworkbook section...I can successfully
run a workbook_open event. I'm not sure if maybe I haven't declared
something correctly, or do I need to pass something to the save event???
Please help! Thanks!

The following is cut from my Thisworkbook section:

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

Call protect

End Sub

Protect is as follows (in my Module 1):

Sub protect()

Dim wb As Workbook
Dim sh As Worksheet

Set wb = ThisWorkbook

Application.ScreenUpdating = False
For Each sh In wb.Worksheets
sh.protect Password:="xxxx", DrawingObjects:=True, Contents:=True,
Scenarios:=True
Next sh
Application.ScreenUpdating = True

End Sub



--
Thanks!

Chris,

Your very welcome. I'm glad it worked.

The best list I know of is the Object Browser in the VBE. Mostly though,
it's just something to be aware of. If you think you're using a keyword,
you can hover over it and press F1. If Help comes up with "Keyword not
Found" then your golden.

Doug

"Calgarychris" wrote in message
...
Doug - you're brilliant...Thanks so much!

Is there a list somewhere of key words to avoid? I have zero VBA
knowledge
aside from one book and a little bit of fumbling.

Thanks again!

Chris
--
Thanks!


"Doug Glancy" wrote:

Calgarychris,

I wonder if it's because you are using an Excel keyword, "Protect", as a
module name? You might try renaming, in any event it's recommended not
to
name variables, etc. with Excel keywords.

hth,

Doug

"Calgarychris" wrote in message
...
Hi,

I've scanned the Q&As for this but I can't seem to get the beforesave
event
to work. All I want to do is, just before saving, a macro would
protect
each
sheet in the spreadsheet. I can run that macro easily enough on its
own,
but
when I call that sub from the beforesub it doesn't seem to fire...it
doesn't
even allow me to step through it.

Obviously I'm very very green at all this...I checked my
application.enableevents was true (I'm not even sure what that does!),
I've
checked that it's located in the Thisworkbook section...I can
successfully
run a workbook_open event. I'm not sure if maybe I haven't declared
something correctly, or do I need to pass something to the save
event???
Please help! Thanks!

The following is cut from my Thisworkbook section:

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

Call protect

End Sub

Protect is as follows (in my Module 1):

Sub protect()

Dim wb As Workbook
Dim sh As Worksheet

Set wb = ThisWorkbook

Application.ScreenUpdating = False
For Each sh In wb.Worksheets
sh.protect Password:="xxxx", DrawingObjects:=True,
Contents:=True,
Scenarios:=True
Next sh
Application.ScreenUpdating = True

End Sub



--
Thanks!