0

I am using the following code to prevent a user from right clicking the tabs in an Excel workbook :

application.CommandBars("Ply").Enabled = false

on the beforeclose event of the workbook, I am calling another procedure which contains 

application.CommandBars("Ply").Enabled = true

Debugging step by step helped me understand the command actually reactivates the right click for the active workbook but it does not for other workbooks/or the application. After closing, if I open another workbook with no protection at all, the right click on tabs is not enabled.

Does anyone have an idea about how to reactivate this right click for all workbooks?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Sébastien
  • 5,263
  • 11
  • 55
  • 116
  • what version of Excel? When I test this out, it seems to work as expected. This is an application level setting, applying it to any workbook affects all workbooks. So when you re-enable it in the `BeforeClose` event, that restores right-click functionality to other workbooks -- at least it does when I test this in Excel 2010. – David Zemens Jul 02 '14 at 21:36
  • well, I have Excel 2013. I know it should reactivate it... so far I can't seem to find the solution. – Sébastien Jul 02 '14 at 21:52

2 Answers2

0

You could try resetting the command bar by using - 

Application.CommandBars("Ply").Reset

Thus as if I understand you are trying to disable right click for the certain workbook so in that workbook you should use -

Application.ActiveWorkbook.CommandBars("Ply").Enabled = False
Han Soalone
  • 902
  • 8
  • 18
  • hmmm, unforunately, the reset is not working either after the workbook is closed and the Application.ActiveWorkbook.CommandBars("Ply").Enabled = False triggers an error – Sébastien Jul 02 '14 at 21:55
  • well, it seems I have kind of a solution: if I use Application.CommandBars("Ply").enabled=true in combination with Application.CommandBars("Ply").Reset it works. Except if i open another workbook while the first one is protected but this is an issue for another day. Thanks!! – Sébastien Jul 02 '14 at 22:28
0

If the goal is to prevent the user from accidentally or otherwise clicking on the Tabs, the best solution is to simply hide them. That can be done directly, or via VBA using 

Dim ws As Worksheet
Set ws = ActiveSheet ' Or whatever sheet you want
ws.Visible = false   ' and as needed during the execution, you may turn this to true
hnk
  • 2,216
  • 1
  • 13
  • 18
  • well, this is a complicated workbook offering different interface configuration. One of them allows the user to navigate between tabs by left-clicking them while not allowing right clicking. So the idea remains hiding the ply commandbar and putting it back. – Sébastien Jul 02 '14 at 22:16
  • Ok. I'm sure there's some reason behind that preset behavior. Good luck. – hnk Jul 02 '14 at 22:20