AddinInstall + Delete custom CommandBar Menu

[Mario]

Hello ,

I found (at http://www.ozgrid.com/VBA/excel-add-in-create.htm ) the
following code on the net and i'm trying to use it:
When adding the AddIn , i get an error message 'Invalid procedure call or
argument'
What is the problem here ?
On adding the AddIn , the code is trying to delete a control that is not
there or not created yet.
Do I need extra code to find the control first before deleting it ?

Thanks for helping me with this .
Mario

Private Sub Workbook_AddinInstall()
On Error Resume Next 'Just in case
'Delete any existing menu item that may have been left.
'********************************************************************
Application.CommandBars("Worksheet Menu Bar").Controls("Super
Code").Delete
'********************************************************************
'Add the new menu item and Set a CommandBarButton Variable to it
Set cControl = Application.CommandBars("Worksheet Menu
Bar").Controls.Add
'Work with the Variable
With cControl
.Caption = "Super Code"
.Style = msoButtonCaption
.OnAction = "StartMCR"
'Macro stored in a Standard Module
End With

On Error GoTo 0

End Sub
Private Sub Workbook_AddinUninstall()
On Error Resume Next 'In case it has already gone.
Application.CommandBars("Worksheet Menu Bar").Controls("Super
Code").Delete
On Error GoTo 0
End Sub

 

[Chip Pearson]

The On Error Resume Next should ignore the error 5 that is raised in
Workbook_AddinInstall if the menu item "Super Code" is not found. Check your
error settings. In VBA, go to the Tools menu, choose Options then the
General tab. Ensure Error checking is set to "Break In Class Module".

The code itself could use some improvement.

First, the control should be added with the Temporary parameter set to True.
Set cControl = Application.CommandBars("Worksheet Menu Bar") _
.Controls.Add(temporary:=True)

Then the control should be given a unique Tag value:

With cControl
.Caption = "Super Code"
.Style = msoButtonCaption
.OnAction = "StartMCR"
.Tag = "TagValue" '<<<< ADDED
'Macro stored in a Standard Module
End With

Then the Delete code should then use the Tag to delete the menu item:

On Error Resume Next 'In case it has already gone.
Application.CommandBars.FindControl(Tag:="TagValue").Delete
On Error GoTo 0


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)