Add-ins
 

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.

Add-ins
 

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)

"Spencer Hutton" wrote in message
...
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.

Add-ins
 

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
(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)

"Spencer Hutton" wrote in message
...
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.




.

Add-ins
 

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(sMe nu).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(sMe nu).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)

"spencer" wrote in message
...
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
(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)

"Spencer Hutton" wrote in message
...
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.




.