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 only one 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!
Excel Instances Creating Multiple Custom Menus
Hot Topic
- VS C++ 2002 .NET problems
- VS 2k5 CR10.2 Deploy=Error in Unknown.Rpt
- How can I get the parent folder path of the selected item?
- Replacing the +/- in a TreeView
- Any way to print a rich text content (like htm stored in database)?
- System.ServiceModel not listed in Visual Studio Add Reference
- .xaml.cs files cannot be edited after installing orcas
- XML serialization and cloning...
- WCF Service-Skeleton from WSDL
- View report from the crystal report server using .NET
Excel Instances Creating Multiple Custom Menus
Radoslav Č&#225&#59;p
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.
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
José Valim
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
Kate Wells
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).