Disabling the use of Menu Items. How?

[Brian]

I have an application workbook where I need to restrict
actions that can be done by the user, specifically I need
to remove some or all of the toolbars and Menubar items
and also disable the use of the right click to customise
and switch on or off toolbars etc.

All that I want to be visible and enabled are the
following:
On the Menu Bar:
File Tools Help
Close Protection About Microsoft Excel
Save
Print Preview
Print
Exit

Also I do not want any of the toolbars visible or enabled.

Can anyone give me guidance as to the code or macros for
how this can be done, it should be set only when the
workbook is opened and active, and should be reset to the
users normal settings when the workbook is closed or not
active.

Thank you in advance for any assistance.

Brian

 

[Harald Staff]

Hi Brian

See if this get you started:

Sub HijackMenu()
Application.CommandBars(1). _
Controls("File"). _
Controls("Save").OnAction = _
ThisWorkbook.Name & "!MySave"
End Sub

Sub MySave()
MsgBox "Save the rainforest", vbExclamation
End Sub

Sub ResetMenu()
Application.CommandBars(1). _
Controls("File"). _
Controls("Save").OnAction = ""
End Sub

Code things for each of them (they do have an Enabled and a Visible property too if you
hate my by far sillier OnAction macros). And call the proper ones from the
Workbook_open/activate/deactivate/close events. It's a dirty job, but someone's gotta do
it.

 

[JON JON]

Hello Brian,

My suggestion is for you to create a custom toolbars that looks the way you
want it and then disabled all other toolbars. The first code below will
show your custom toolbar (in this case "My Custom") and disable the all
other toolbars when the workbook is activated. The second code will do the
reverse when the workbook is deactivated. This is a workbook event and such
should be in the thisworkbook module.

Private Sub Workbook_Activate()
Application.CommandBars("My Custom").Visible = True
For Each cbar In Application.CommandBars
If cbar.Name <> "My Custom" Then
cbar.Enabled = False
End If
Next cbar
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("My Custom").Visible = False
For Each cbar In Application.CommandBars
cbar.Enabled = True
Next cbar
End Sub

caveat: I believe you know that this will not prevent user to use keyboard
short-cut to do commands not available in your menu. e.g. ctrl+o

Regards,

Jon-jon

 

[Brian]

Thank you for the Code, it works fine.

I have managed to get code to disable the Alt key,
although the worksheets are locked and password protected
any way so there should be no problem there, famous last
words
Thanks again.
Brian