01-28-2004, 10:49 AM
Join Date: Jul 2003
Finding a menu in a commandbar collection
I found some code that will create a custom menu and the code is stored within a workbook. Once the workbook is opened it adds the menu to the main menu. See code below:
Public Sub AddCustomMenu()
Dim cbWSMenuBar As CommandBar
Dim muCustom As CommandBarControl
Dim iHelpIndex As Integer
Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpIndex = cbWSMenuBar.Controls("Help").Index
Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpIndex, _
.Caption = "&Finance UK Tools"
.Caption = "&Batch Email"
.OnAction = "c:\Program Files\FinanceUKTools.xls!CreateBatchMail"
.Caption = "&Download to Access"
.OnAction = "c:\Program Files\FinanceUKTools.xls!ADOFromExcelToAccess"
The actual procedures it references are also stored in the same workbook, so when the workbook reopens it will create another menu. What I would like it to do is to check whether the commandbar already exists, I've put together the following code but I don't think I'm looking at the right object because it is going through the toolbars not the menus. Can somebody point me in the right direction.
Private Sub Workbook_Open()
Dim c As CommandBar
Dim bolfound As Boolean
For Each c In Application.CommandBar
If c.Name = "Finance UK Tools" Then
bolfound = True
If found <> True Then