Allow my vba code to always run when spreadsheet opened

[Simon Caiger]

Hi,

I hope I haven't just wasted a few hours for nothing.

Manufacturing asked me to write some vba code behind one of their spreadsheets to prevent guys on the shop floor accidentally editing fields that they should not normally need to edit.
The protected cells are password protected and everything is working fine.

I don't get to do vba very often but I notice that on loading the form, if the user declines to allow macros to run, my code does not run and the user can edit whatever cells they want.

I appreciate the reason for the macro warning (viruses).
I also realise that you can change the protection level. Trouble with that is we are then open to virus attack and also the computer setting could be changed back again.

Is there any way to get my vba code to run irrespective of the macro protection level setting (I suspect the answer to that will be no)?
Alternatively, is there any way that I can prevent any of the sheet from being edited if macros are disabled?

Any help appreciated.

[Shattered]

Is there any way to get my vba code to run irrespective of the macro protection level setting (I suspect the answer to that will be no)?

Short Answer - No. Long Answer - Yes. It can actually be done but I STRONGLY advise against it.

Alternatively, is there any way that I can prevent any of the sheet from being edited if macros are disabled?

Protection for the sheet is not dependant on macros being run, so as long as the passwords are not known by everyone it shoudl be easy to set the spreadsheet so that all cells on all sheets are passworded. If the macros are loaded, the first macro can un protect the cells that would normally need editing.

[Simon Caiger]

Thanks for the reply,

I accept your STRONG advice and will apply the second method.

What I plan to do is have all cells on the sheet locked and passworded.

If macros are disabled, the sheet just won't get unlocked.

If macros are enabled, the first action will be to unlock the sheet with the password.

Any chance of a code snippet showing how to unlock the entire sheet with a password?

[Shattered]

Sub Protect_On()
ActiveSheet.Protect "Pass"
End Sub

Sub Protect_Off()
ActiveSheet.Unprotect "Pass"
End Sub

In this instance the password is obviously just Pass - but you get the idea....

Protect_On should run when the spreadsheet is exited
Protect_Off should run when the spreadsheet is loaded

[Simon Caiger]

Thanks Shattered