Application.commandbars("Cell").("?????").delete

Question

Unintended consequences ...

What worked in Excel 2007 with multiple document interface no longer works in Excel 365 with single document interface.

Using a workbook with the four command bar items below works fine.  However when that workbook is closed the below code fires and deletes the custom items.  However, the menu items remain in all subsequent opened workbooks.  I don't want that.  When the macro workbook closes, I want the custom menu items to close as well.

This code worked in 2007, and would not stay for subsequent workbooks.  Why not Office 365?

Sub RidIt()

 CommandBars("Cell").Controls("Add Comments").Delete

 CommandBars("Cell").Controls("Customer Detail").Delete

 CommandBars("Cell").Controls("Load Items").Delete

 CommandBars("Cell").Controls("Load Payments").Delete

End Sub

Answer 1

I do not know what could be the problem.

Private should not be an issue.

I simply used a MsgBox, not actual CommandBars for testing.

May be a 365 Home issue but I am way behind that in my Excel version.

Gord

Answer 2

How did the macro Ridit get initiated in 2007 when you closed the workbook?

Suggest you call the Ridit macro from beforclose event.

Place in Thisworkbook module.

Sub Workbook_BeforeClose(Cancel As Boolean)

    Ridit

End Sub

Gord

Gord,

Thanks for the reply.

This is exactly how I am doing it

Thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)

RidIt

End Sub

Is 'Private' causing my issue?

Answer 3

How did the macro Ridit get initiated in 2007 when you closed the workbook?

Suggest you call the Ridit macro from beforclose event.

Place in Thisworkbook module.

Sub Workbook_BeforeClose(Cancel As Boolean)

    Ridit

End Sub

Gord