Hide menu bar control

[Guest]

Dear experts,

How I can hide a menu bar control (Format) for one worksheet or one workbook
only. I've searched the web but cannot find an appropriate answer. Please
help.

Thanks in avance

 

[papou]

Hello Freshman
Place this code in Thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars(1).FindControl(ID:=30006).Enabled = True
End Sub

Private Sub Workbook_Open()
Application.CommandBars(1).FindControl(ID:=30006).Enabled = False
End Sub

With this code the Format cell option will still be available in the
contextual menu, if you also wish to disable this option add:
Application.CommandBars("Cell").FindControl(ID:=855).Enabled = False

HTH
Cordially
Pascal

NB:
To place code in Thisworkbook :
right click on the Excel icon situated immediately before the File menu of
Excel
select View Code
paste code suggested

 

[Guest]

Maybe you can even put them into:

Private Sub Workbook_activate
(instead of open)
and
Private Sub Workbook_deactivate
(instead of before close)

that way you will see it even, when you work in 2 workbooks simultaneously

Cheers Carlo

 

[papou]

Hello Carlo
Absolutely! Good point.

Cordially
Pascal

 

[Guest]

Hi Pascal,

Thanks for your codes and they did work well. Thanks again for your help.
Have a good day.

Best regards.

 

[Guest]

Hi Carlo,

Thanks for your additional tips. Regards.

 

[papou]

My pleasure!

Have a good day.

Same to you.

Cordially
Pascal