Disable Tools > Protection

[TimN]

Thanks to a post from Ron de Bruin I found the following to disable the Tools

Protection option from the tool bar:

Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=30029, Recursive:=True).Enabled = False

My question is where do I insert this code? Is it in the "This Workbook"
portion of the code? I gave that a try, but nothing happened.

I have protected all the cells where I do not want the user to change a
formula. However, if they try to change a formula they get the Excel message
box that advises them the cell is protected, then tells them how to go
unprotect the worksheet.

At first I password protected the sheet, but then my macro wouldn't run. I
was unsuccessful in writting code to unprotect, run the macro, then protect
the sheet. So, I decided to try just removing the tools>Protection option
from the menu so the user will not have any easy time unprotecting the sheet
and changing formulas.

If anyone has a better solution, please advise.

Thanks!

 

[Ron de Bruin]

Hi Tim

Use this two events in the Thisworkbook module.

Read more about events on Chip Pearson's site.
http://www.cpearson.com/excel/events.htm


Private Sub Workbook_Activate()
'your code to change Enabled to False
'Or the name of your macro
End Sub

Private Sub Workbook_Deactivate()
'your code to change Enabled to True
'Or the name of your macro
End Sub

If you want to use your macro to protect and unprotect

Then use it like this (ron is the password)

Sub test()
With Sheets("Sheet1")
.Unprotect "ron"
.Range("B1").Value = "Hi"
.Protect "ron"
End With
End Sub

 

[TimN]

Ron,

Thanks!!! I got rid of the tools > protection option. I am going to work
with the code for the password. May have to get back with you on that with
more questions.

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/menuid.htm