0

I want to block out some of the command bar options, but the method I am currently using is a bit too restrictive. I currently use this code:

Sub Auto_Open()
Application.CommandBars.FindControl(ID:=847).Enabled = False
End Sub

Sub Auto_Close()
Application.CommandBars.FindControl(ID:=847).Enabled = True
End Sub

The problem with this is that as long as the workbook that has this code stays open no other workbook can use voided controls. I think a solution might be to use:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range,  Cancel As Boolean)
Application.CommandBars.FindControl(ID:=847).Enabled = False
End Sub

This will stop the user from using my voided controls when they right click, say to delete a sheet.

I now need a way to remove those voids as soon as they click away, so the controls are available to use in any other workbooks they may have open. Is there an event that I can use for this which isn't so common that it will make the code run every 10 seconds? I also wondered whether you can use delays in vba to run a piece of code 5 seconds after a previous piece of code?

user3737057
  • 121
  • 3
  • 12
  • You can run a macro at a specific time with Application.OnTime but this isn't a very reliable way as you may end up blocking the UI in case of an error. –  Jul 04 '14 at 07:40
  • Which version of Excel are you using? I haven't seen `Auto_Open` used in preference to `Workbook_Open` for a long time. – Mark Fitzgerald Jul 04 '14 at 10:07
  • 2007, but don't they do the same thing? – user3737057 Jul 04 '14 at 10:19
  • They do but `Workbook_Open` is addressed before `On_Open` which I have tested by placing `Stop` commands in both. `On_Open` is only provided in Excel 2007 and beyond for backward compatibility. – Mark Fitzgerald Jul 04 '14 at 10:28

1 Answers1

0

In the workbook module in which you want to disable the command bar options enter:

Private Sub Workbook_Activate()
    Application.CommandBars.FindControl(ID:=847).Enabled = False
End Sub

Private Sub Workbook_Deactivate()
    Application.CommandBars.FindControl(ID:=847).Enabled = True
End Sub

Whenever you are working in the workbook the commands should be disabled. I'm not certain it will work in Excel 2007 because Application.CommandBars.FindControl(ID:=847).Caption is &Delete and Delete is still enabled on both the cell right click and tab right click.

In Excel 2007 you can disable Ribbon commands for the active workbook but it's not easy. You have to change the workbook extension to .zip then edit the .rels file in _rels folder within the zip. Change the extension back to what it was after editing and the targeted commands or groups should be disabled. Disabling the Ribbon command also disables any associated right-click commands. The kicker is that sheet add/delete commands aren't available in the Ribbon.

Mark Fitzgerald
  • 3,048
  • 3
  • 24
  • 29
  • 1
    @lowak I considered that but the OP stated "some commands" so I wanted to keep it general. The comment around `&Delete` was only to illustrate that `Application.CommandBars` isn't applicable to Excel 2007 or later. It won't cause an error but ...it does nothing! – Mark Fitzgerald Jul 04 '14 at 12:10