Search code examples
vbaexcelcommandbar

CommandBars("Ply") won't reset on workbook beforeclose VBA Excel

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?

Solution

  • 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