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:=85 5).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



"Freshman" a écrit dans le message de
news: ...
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

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" wrote:

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:=85 5).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



"Freshman" a écrit dans le message de
news: ...
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 Carlo
Absolutely! Good point.

Cordially
Pascal

"Carlo" a écrit dans le message de news:
...
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" wrote:

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:=85 5).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



"Freshman" a écrit dans le message
de
news: ...
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

Hi Pascal,

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

Best regards.

"papou" wrote:

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:=85 5).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



"Freshman" a écrit dans le message de
news: ...
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

Hi Carlo,

Thanks for your additional tips. Regards.

"Carlo" wrote:

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" wrote:

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:=85 5).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



"Freshman" a écrit dans le message de
news: ...
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

My pleasure!

Have a good day.
Same to you.

Cordially
Pascal

"Freshman" a écrit dans le message de
news: ...
Hi Pascal,

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

Best regards.

"papou" wrote:

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:=85 5).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



"Freshman" a écrit dans le message
de
news: ...
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