custom command bar question

[rju]

I've developed 3 programs for a client...they want easy access to th
programs and associated user forms...didn't think it was an
biggy...wroted the code for a custom menu and associated macros...work
fine, well kinda...

when I launch the item selected from the command bar, the program firs
opens up the original workbook I wrote the code in, then goes to th
correct program or user form...

should I delete the custom item and paste the code into each progra
and launch on open, delete on close...I thought writing the code wa
the tuff part! Kinda frustrating since I see it working, just no
directly.

Thanks,
Ro

 

[MSP77079]

You are exactly correct. You need to have the Add_Toolbar macro in eac
workbook. Auto load the toolbar when the workbook opens. Auto delet
the toolbar when the workbook closes

 

[Jon Peltier]

Ron -

When I make a custom menu like this, I always qualify the reference to
the macro with the workbook name:

With Application.CommandBars(i)
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Run the Macro"
.Style = msoButtonCaption
.OnAction = ThisWorkbook.Name & "!RunTheMacro"
End With
End With

This way, the button always refers to the workbook that opened the menu.
The menu is built when the workbook opens, and destroyed when it closes,
so a button never refers to a macro in a closed workbook.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[MSP77079]

Thanks Ron! That was a valuable addition

 

[rju]

Thanks for the insight...

I included the following codes (below) to insert the custom item int
the Command Bar with a workbook Open event...runs just fine...
I inserted the next piece of code to delete the custom item from th
Command Bar with a BeforeClose event...not so fine! The code does no
trigger when I close the workbook...any thoughts??? I executed th
"delete" code as a macro to see if it works and it does...wierd?

Thanks,
Ron

Private Sub Workbook_Open()
Dim cbpop As CommandBarControl
Dim cbctl As CommandBarControl
Dim cbsub As CommandBarControl
Dim cbctl2 As CommandBarControl
'*****************************************************
' Create a popup control on the main menu bar
Set cbpop = Application.CommandBars("Worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup)
cbpop.Caption = "&Britannia"
cbpop.Visible = True
'*****************************************************
' Adds a popup for a submenu to this menu
Set cbsub = cbpop.Controls.Add(Type:=msoControlPopup)
cbsub.Visible = True
cbsub.BeginGroup = True
cbsub.Caption = "&Program"
'*****************************************************
' Add a menu item (1) to the submenu
Set cbctl = cbsub.Controls.Add(Type:=msoControlButton)
cbctl.Visible = True
cbctl.Style = msoButtonCaption
cbctl.Caption = "&AZproject"
' *** Action to perform
'cbctl.OnAction = "OpenAZproject"
'*****************************************************
' Add a menu item(2) to the submenu
Set cbctl = cbsub.Controls.Add(Type:=msoControlButton)
cbctl.Visible = True
cbctl.Style = msoButtonCaption
cbctl.Caption = "&DRSv2.0"
' Action to perform
'cbctl.OnAction = "OpenInvoiceIM"
'*****************************************************
' Add a menu item(3) to the submenu
Set cbctl = cbsub.Controls.Add(Type:=msoControlButton)
cbctl.Visible = True
cbctl.Style = msoButtonCaption
cbctl.Caption = "&InvoiceIM"
' Action to perform
'cbctl.OnAction = "OpenDRS"
'*****************************************************
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cbWSMenuBar As CommandBar
On Error Resume Next
Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
cbWSMenuBar.Controls("Britannia").Delete
End Su

 

[rju]

Answer to my own question:

Here's what I did:

Earlier when I posed my question, the macro I refered to in order t
test code validity was named Delete (how original!)...code as follows:

Sub Delete()
Dim cbWSMenuBar As CommandBar
On Error Resume Next
Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
cbWSMenuBar.Controls("Britannia").Delete
End Sub

the BeforeClose sub now looks like the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Delete
End Sub

Works just fine. Hope this helps someone.

Ro