In Microsoft Excel is it possible to password protect the options to stop
them being adjusted in a protected worksheet

Mark

If you insert the following VBA line into your project then the 'Options'
menu will not be available:

commandbars("Worksheet Menu
Bar").Controls("Tools").Controls("Options").Enable d=False

If you put this in the OpenWorkbook Event, then as soon as your workbook is
opened the code will run and the options menu will be disabled.
It is of course good practice to also put a similar line into your
CloseWorkbook event code, but change the 'False' statement to true, so that
the option is again available.

If you wish to disable the whole of a particular menu tree, youe could
truncate the line, so to diable the entire 'Tools' menu tree the line would
be:

commandbars("Worksheet Menu Bar").Controls("Tools").Enabled=False

Note - the above code samples are on one continuous line, not two as
displayed on screen.

HTH


Neil
www.nwarwick.co.uk
"Mark" wrote:

In Microsoft Excel is it possible to password protect the options to stop
them being adjusted in a protected worksheet

Neil,
Maybe I'm not entering it correctly in VB...not sure but cannot get it to
work. Can you please expand on the instructions on where to place the line.

Thanks...Mark

"Neil" wrote:

Mark

If you insert the following VBA line into your project then the 'Options'
menu will not be available:

commandbars("Worksheet Menu
Bar").Controls("Tools").Controls("Options").Enable d=False

If you put this in the OpenWorkbook Event, then as soon as your workbook is
opened the code will run and the options menu will be disabled.
It is of course good practice to also put a similar line into your
CloseWorkbook event code, but change the 'False' statement to true, so that
the option is again available.

If you wish to disable the whole of a particular menu tree, youe could
truncate the line, so to diable the entire 'Tools' menu tree the line would
be:

commandbars("Worksheet Menu Bar").Controls("Tools").Enabled=False

Note - the above code samples are on one continuous line, not two as
displayed on screen.

HTH


Neil
www.nwarwick.co.uk
"Mark" wrote:

In Microsoft Excel is it possible to password protect the options to stop
them being adjusted in a protected worksheet