Excel Instances Creating Multiple Custom Menus

Hello all. I have a question to which searching and research has yielded no answers. I followed the MSDN blog here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/odc_VSTCommBar.asp about creating custom menu items in Excel. This project is for an end user that needs to open multiple instances of this file. However, when multiple instances of this file are opened, it creates that many instances of custom menus. If the file opens five times, there are five custom menus listed. My question is how do I consistantly make one menubar appear across all the Excel instances? For example, if the file opens five times, I want onlyone custom menu, not five. This has become bothersome, as I have been searching for quite some time. Does anyone have a solution to this problem? Any advice would be appreciated. Thanks!

[861 byte] By [mlp232] at [2007-12-16]

«« 2 questions about Sql Data Adapter
»» how to read a certain line of textfile?

# 1

I think you can prevent this behavior by checking to see if the menu item exists in the ThisWorkbook_Open event and if it does obtain a reference to the menu rather than creating a new menu each time.

For example, I modified the code in the ThisWorkbook_Open as follows:

ThisWorkbook_Open
===============================

' Called when the workbook is opened.

Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open

'Check to see if the menu is present if it is obtain a reference to the
'menu and menu bar item. If the menu is not present create it.
If Not IsNothing(ThisApplication.CommandBars("Worksheet Menu Bar").FindControl(, , "&Custom Code")) Then

MainMenuBar = ThisApplication.CommandBars("Worksheet Menu Bar")

MenuBarItem = MainMenuBar.FindControl(, , "&Custom Code")

MenuItem = CType(MenuBarItem, Office.CommandBarPopup).Controls(1)

Else

' Create menu
InitMenuBarItems("&Custom Code")

' Create menu item control.
MenuItem = CreateButton( _

DirectCast(MenuBarItem, Office.CommandBarPopup), _

"Run Demo Code")

End If

End Sub

===============================

I also modified the code from the url that you referenced (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/odc_VSTCommBar.asp ) and added a line of code to set the Tag property of the menu bar to make it easier to search for. I modified the InitMenuBarItems subroutine as follows:

InitMenuBarItems
==============================

Private Sub InitMenuBarItems(ByVal Caption As String)

Try

MainMenuBar = ThisApplication.CommandBars( _

"Worksheet Menu Bar")

MenuBarItem = MainMenuBar.Controls.Add( _

Office.MsoControlType.msoControlPopup, Temporary:=True)

MenuBarItem.Caption = Caption

MenuBarItem.Tag = Caption 'Added to make searching easier.

Catch ex As Exception

MessageBox.Show(ex.Message, _

ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

==============================

The remainder of the code I left as is.

Upon testing with these changes I only created one instance of the menu item.

Hope this helps!

Regards,

Ken Laws
MSFT

This posting is provided "AS IS" with no warranties, and confers no rights.

For more information regarding Visual Studio Tools for Office 2005:

Best of Blogs: Visual Studio 2005 Tools for Office
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_2003_ta/html/odc_landvsto2005_ta.asp

Visual Studio Tools for Office Forum
http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=16

Visual Studio Tools for the Microsoft Office System
http://msdn.microsoft.com/office/understanding/vsto/default.aspx

KenLaws at 2007-9-9 >

top of Msdn Tech,Visual Studio Tools for Office,Visual Studio Tools for Office...

# 2

Thanks a lot, Ken. The code you posted just creates one menu bar called 'Custom Code.' However, is there a way to have multiple instances of this file open at the same time while only creating one menu bar called 'Custom Code?' As it stands, the code you posted allows me to create just one single menu bar called 'custom code' but doesn't allow me to open more than one instance of the file. Running the file from VST 2003 and double-clicking the Excel file will bring up the file that is already opened, only reloading it to the data contained within the locally saved file.
In summary, I would like to have multiple instances of Excel open while at the same time retaining only one custom menu bar per Excel instance (one '&Custom Code' per Excel instance).

mlp232 at 2007-9-9 >

# 3

The code modifications that I provided should have no effect on the manner in which Microsoft Excel opens a file as I see the same behavior that you've described by double-clicking on an Excel file when testing with an Excel file that isn't part of a VSTO solution.

Based on your response it would seem that the best solution would be to use an Excel template with the VSTO project which would allow you to create a new workbook from the template, however, I know Excel Templates were not an option for VSTO 2003, but I wanted to let you know that they will be an option for VSTO 2005.

You can open multiple instances of the file with only one instance of the custom menu bar being displayed, but you would need to open multiple instances of Microsoft Excel and then browse to and open the file in each instance. Please note that opening multiple instances of the workbook from the same location will cause you to receive an Read-only prompt when opening the second instance and any instance thereafter of the workbook.