Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|