MrExcel.com - Your One Stop for Excel Tips & Solutions

Disable menu functions

Posted by Samantha on November 26, 2001 6:33 AM

Does anybody know what the code is to disable just one item on the menu bar, for example the View/Formula Bar function? (I mean not to delete or hide the Formula bar but rather gray out its function on the menu so people cannot use it)
Thanks,
Samantha

Posted by Bib on November 26, 2001 7:01 AM


Hi,

If you want users not to change formulas, try to use the Format/Sheet/Protect or Format/Cell/Protect functions...

cheers

Posted by ian Bartlett on November 26, 2001 7:22 AM

Samantha,

The following code disables the cut, copy & paste functions, I think you can modify it for other functions. The second block of code reenables them:

Private Sub Workbook_Activate()
' Turn off the menu
Application.CommandBars("Edit").Controls(3).Enabled = False
Application.CommandBars("Edit").Controls(4).Enabled = False
Application.CommandBars("Edit").Controls(5).Enabled = False
Application.CommandBars("Edit").Controls(6).Enabled = False
' Turn off the toolbar:
Application.CommandBars("Standard").Controls(7).Enabled = False
Application.CommandBars("Standard").Controls(8).Enabled = False
Application.CommandBars("Standard").Controls(9).Enabled = False
Application.CommandBars("Standard").Controls(10).Enabled = False
' turn off shortcutkeys:
Application.OnKey "^c", ""
Application.OnKey "^v", ""
Application.OnKey "^x", ""
End Sub

Private Sub Workbook_Deactivate()
' Enable the menu:
Application.CommandBars("Edit").Controls(3).Enabled = True
Application.CommandBars("Edit").Controls(4).Enabled = True
Application.CommandBars("Edit").Controls(5).Enabled = True
Application.CommandBars("Edit").Controls(6).Enabled = True
' Enable the commandbar:
Application.CommandBars("Standard").Controls(7).Enabled = True
Application.CommandBars("Standard").Controls(8).Enabled = True
Application.CommandBars("Standard").Controls(9).Enabled = True
Application.CommandBars("Standard").Controls(10).Enabled = True
' Enable the shortcut keys:
Application.OnKey "^c"
Application.OnKey "^v"
Application.OnKey "^x"
End Sub


To disable the user from "right-clicking" on the userform,
and still be able to cut, copy or paste, you can disable
the right-mousebutton by adding the following code to the VBA-sheet:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
Cancel = True
End Sub

Is this what you were after?

Regards,

Ian