Hide menu bar control

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Hi Pascal,

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

Best regards.
 
Back
Top