I have used a combination of locking certain cells and then protecting
worksheet in order to prevent accidentally overwriting cells with formulae in
them. This works fine for what I want but occasionally I need to change a
formula so have to unprotect the sheet. Again, no problem but what I am
looking for is a reminder to reprotect the worksheet after I have amended the
formulae I need to change. I guess there is a macro or something I could
write but please bear with me since I am unfamiliar with writing macros.
Also, is it possible for this to be aplied to all sheets in the workbook?

Colin,

You could try this:-

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sht As Worksheet
For Each sht In Sheets
sht.Select
If ActiveSheet.ProtectContents = False Then
MsgBox (sht.Name & " isn't protected")
End If
Next sht
End Sub


Alt+F11 to open VB editor. Double click 'This workbook. On the right hand
side from the dropdown select 'workbook'. In the other dropdown select
'before save and paste this code in.

Was that any use?

Mike

"Colin G" wrote:

I have used a combination of locking certain cells and then protecting
worksheet in order to prevent accidentally overwriting cells with formulae in
them. This works fine for what I want but occasionally I need to change a
formula so have to unprotect the sheet. Again, no problem but what I am
looking for is a reminder to reprotect the worksheet after I have amended the
formulae I need to change. I guess there is a macro or something I could
write but please bear with me since I am unfamiliar with writing macros.
Also, is it possible for this to be aplied to all sheets in the workbook?

Unprotect ur sheet
Start Macro recorder
Protect ur sheet
Stop recorder

now u got a code to protect ur sheet

There are difrent way's to start that code eg.
Everytime u save ur sheet
When u select another sheet
When u select another cell
When u change a cell
When u open ur sheet
and some others too

whitch one do u vant ?


"Colin G" skrev:

I have used a combination of locking certain cells and then protecting
worksheet in order to prevent accidentally overwriting cells with formulae in
them. This works fine for what I want but occasionally I need to change a
formula so have to unprotect the sheet. Again, no problem but what I am
looking for is a reminder to reprotect the worksheet after I have amended the
formulae I need to change. I guess there is a macro or something I could
write but please bear with me since I am unfamiliar with writing macros.
Also, is it possible for this to be aplied to all sheets in the workbook?

Thank you both. Not sure I fully understand the detail Mike but will give it
a try. Excellent, I am probably looking to do it everytime I save sheet or
workbook. It is really as a reminder if I forget to re-protect the sheet
having made amendments.

The macro will loop through every sheet and if it isn't protected you will
get a message pop up.

Mike

"Colin G" wrote:


Thank you both. Not sure I fully understand the detail Mike but will give it
a try. Excellent, I am probably looking to do it everytime I save sheet or
workbook. It is really as a reminder if I forget to re-protect the sheet
having made amendments.

ok i look like Mike allready did the job :-)


"Colin G" skrev:


Thank you both. Not sure I fully understand the detail Mike but will give it
a try. Excellent, I am probably looking to do it everytime I save sheet or
workbook. It is really as a reminder if I forget to re-protect the sheet
having made amendments.

Thanks again, will give it a go

I have opened VB editor and have copied macro in but when I close
spreadsheet it says there is a syntax error. It appears to be pointing to the
second row (Dim sht ....) but not sure. Because I have no idea on how VB
works I don't know where to start looking in order to correct whatever I have
done wrong.

Any further help would be appreciated

The question might be out of date still i give it a shot :
Looks like your still in the developing phase...
I suggest :
unprotect all sheets and/or workbook in One click
Work is done ?
protect your sheets and/or workbook
Check the Add-in James 2007 available @ www.allocator.nl
your welcome