Temporarily disable a menu item - Excel

[MO22]

How do I disable (hide) a menu item for just the current worksheet?

I have a shared workbook, and despite threatening all 15 users with death if they change the Freeze Panes from the settings I have chosen, they continue to change the freeze and deny deny deny.

I know how to take Freeze Panes out of the Windows menu using Customize, but then it's not available for their other personal worksheets. I want to hide it only for the shared sheet. Help!

 

[acron]

Try using the WindowActivate event of the Thisworkbook object of the woorkbook concerned. Justplace you code to disable the menu item in

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    'Code goes here
End Sub

Them reset the menu iten in the corresponding Workbook_WindowDeactivate event.

Both go in the ThisWorkbook code module.

Whenever your workbook is activated the menu is disabled, and when th eworkbook is deactivated the menu item is reset.

A.C.

 

[MO22]

OK, I now know where to put the code, but could you please direct me as to what code to use to disable a menu item? Thanks

 

[Loomah]

Hi,

You could try something like this

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CommandBars("Tools&quot.Enabled = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CommandBars("Tools&quot.Enabled = True
End Sub

Although I'm not sure how to specify a sub menu in the code, at the moment.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

Hi,

You could try something like this, as suggested by acron

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CommandBars("Tools&quot.Enabled = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CommandBars("Tools&quot.Enabled = True
End Sub

Although I'm not sure how to specify a sub menu in the code, at the moment.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[MO22]

Thanks, that worked very well. However, now the administrator is requesting that they be able to set the frozen panes first, then run a macro to disable freeze for other users. No problem, showed her how to do that, but it appears that Excel does not have a corresponding "Unfreeze Panes" command, although it shows up that way on the menu.

 

[acron]

The Freeze/Unfreeze commands are really a toggle.

    ActiveWindow.FreezePanes = True - Freezes the active window
    ActiveWindow.FreezePanes = False - unfreezes the active window

You can use the following as a toggle in your code :

    ActiveWindow.FreezePanes = Not ActiveWindow.FreezePanes

A.C.