add a toolbar button for an add-in

[Spencer Hutton]

I have an add-in. when it is selected from the add-ins list, a toolbar
button shows up on the formatting toolbar thanks to some code donated to me.
however there was also some code to get rid of the toolbar button when the
add-in was deselected and it does not work properly. can someone help me
debug this and help me get rid of this button when the add-in is closed.
thank you.


Dim sMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

sMenu = "myButton"

On Error Resume Next

Application.CommandBars("Formatting").Controls("MarginCalculator").Delete
On Error GoTo 0
End Sub







Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Dim newMenu As Object 'CommandBarControl
Dim ctrlButton As Object 'CommandBarControl

sMenu = "Margin Calculator"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Formatting")
Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)

With oCtl
.BeginGroup = True
.FaceId = 652
.Style = msoButtonIconAndCaption
.OnAction = "ShowCalculator" 'SHOWS USERFORM ASSOCIATED WITH
ADD-IN
End With
SetEnterDefault 'CODE TO MAKE A CERTAIN BUTTON DEFAULT=TRUE
End Sub

 

[Bob Flanagan]

Your code to remove is correct - if the name of the control is
"MarginCalculator" without a space. To check, just see what the tooltip is
on the button. That is the name of the control.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Bob Phillips]

Spencer,

In the create code you delete a control called 'Margin Calculator', but in
the BeforeClose code you try to delete 'MarginCalculator', no space.

Also, where does the control get assigned a name?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)