Hide menu bar control

F

Freshman

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
 
P

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
 
C

Carlo

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
 
F

Freshman

Hi Pascal,

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

Best regards.
 
Top