01-28-2004, 10:49 AM
|
|
Freshman
|
|
Join Date: Jul 2003
Posts: 48
|
|
Finding a menu in a commandbar collection
|
Hi,
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:
Code:
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, _
Temporary:=False)
With muCustom
.Caption = "&Finance UK Tools"
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Batch Email"
.OnAction = "c:\Program Files\FinanceUKTools.xls!CreateBatchMail"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Download to Access"
.OnAction = "c:\Program Files\FinanceUKTools.xls!ADOFromExcelToAccess"
End With
End With
End Sub
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.
Code:
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
End If
Next
If found <> True Then
Call AddCustomMenu
End If
Cheers
Tom |
|
Linear Mode
