Add-ins

S

Spencer Hutton

i created a small application, a userform with 3 pages to calculate margin
and cost and sales price given any of the other 2 variables. i want to make
this an add-in. i have created a toolbar button from which i want the
userform to open from. i saved the workbook as an add in, and selected the
add in from the menu. what i want to know is, how do i get the menu button
to only show up when the add in is selected? and if the add in is
de-selected from the drop down menu, have the button go away. TIA.
 
B

Bob Phillips

Spencer,

You can add the menu on opening the add-in workbook. This is an example

Dim sMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("Tools"). _
Controls(sMenu).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 = "myAddInMenu"

On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("Tools"). _
Controls(sMenu).Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Worksheet Menu Bar")
Set oCtl = oCB.Controls.Add(Type:=msoControlPopup, temporary:=True)

With oCtl
.Caption = sMenu
Set ctrlButton = .Controls.Add(Type:=msoControlButton, ID:=1)
With ctrlButton
.Caption = "mySubMenu"
.Style = msoButtonCaption
.OnAction = "myMacro"
End With
End With

End Sub

This code should be placed in the ThisWorkbook code module.

--

HTH

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

spencer

That works fine, but it creates a menu and sub-menu, is
there a way for it just to be a button with an image? i
have created a custom toolbar called "Margin Calculator"
with that button image on it. i want that toolbar to
show up when the workbook is loaded and go away when it
is closed.
-----Original Message-----
Spencer,

You can add the menu on opening the add-in workbook. This is an example

Dim sMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("Tools"). _
Controls(sMenu).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 = "myAddInMenu"

On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("Tools"). _
Controls(sMenu).Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Worksheet Menu Bar")
Set oCtl = oCB.Controls.Add(Type:=msoControlPopup, temporary:=True)

With oCtl
.Caption = sMenu
Set ctrlButton = .Controls.Add
Click to expand...
(Type:=msoControlButton, ID:=1)
 
B

Bob Phillips

Using the same principle, putting it on the Formatting menu

Dim sMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

sMenu = "myButton"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMenu).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
.Caption = sMenu
.FaceId = 197
.Style = msoButtonIconAndCaption
.OnAction = "myMacro"
End With

End Sub

--

HTH

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