Walkthrough: Create a custom menu item in Excel
Brian A. Randell
MCW Technologies, LLC
September 2003
Applies TO:
Microsoft® Visual Studio® Tools for the Microsoft Office System
Microsoft Office Excel 2003
Microsoft Visual Studio .NET 2003
Overview: Office CommandBar object provides a way to add menu items and toolbar buttons code. In this exercise, you will build the menu bar under the custom menu item and add the code to respond to the Office menu.
content:
Introduction
Establishing a menu and toolbar project is a core feature of Microsoft Office. Although this test is to use these items in Microsoft Office Excel 2003, these operations are similar in Office Word. (Different situations are Menu Bar in Word, and called Worksheet Menu Bar in Excel.) You will build a menu item in the Excel main menu. Then you add this menu item. Finally, you add Click event code to perform custom code.
Tip: The Object Module of the Office menu and toolbar defines in Office.dll, and when you create a new Visual Studio Tools to the Microsoft Office System project, Microsoft Visual Studio® .NET automatically contains references to this module.
prerequisites
To do this, the following software and components must be installed:
• Microsoft Visual Studio .NET 2003 or Microsoft Visual Basic® .NET Standard 2003
• Microsoft Visual Studio Tools for the Microsoft Office System
• Microsoft Office Professional Edition 2003
Tip: If you are a Visual Basic .NET programmer, you need to set the Option Strict to ON (or add an Option Strict declaration in each module). Although this is not necessary, this can guarantee that you don't perform unsafe type conversion. In the later time, the benefits of this option will be much greater than the difficulty of increasing the code.
Start
You will start by building an Excel project for a new Visual Studio .NET.
create project
Use Microsoft Office System Visual Studio Tool to build a new Excel workbook project (in Visual Basic .NET or C #).
Create an Excel workbook project
1. Start Visual Studio .NET, on the File menu, point to New, click on the project.
2. On the Project Type Panel, extend the Microsoft Office System project, then select the Visual Basic project or Visual C # item.
3. Select the Excel workbook in the template panel.
4. Named Excelcommandbars and then stored on the local hard drive.
5. Accept the default value in the Microsoft Office Project Wizard, click Finish.
Visual Studio .NET opens thisworkbook.vb in the code editor or thisworkboo.cs file.
Establish a menu bar project
Creating a menu bar on an Excel main menu item requires you to add a CommandBarControl using the Add method. Create a menu bar project in Excel
1. Add the following variables below the Save Volume Thisapplication and thisworkbook:
'Visual Basic
Private mainmenubar as office.commandbar
Private menubarItem as office.commandbarcontrol
Private Withevents MenuItem as office.commandbarbutton // C #
Private office.commandbar mainmenubar = null;
Private office.commandbarcontrol menuBarItem = null; private office.commandbarbutton menuitem = null;
2. Add the following programs in the OfficeCodeBehing class (established by project template), which initializes the previously declared MainMenubar and Menuitembar objects.
'Visual Basic Private
SubiniTmenUBaritems (Byval Caption As String)
Try mainmenubar = trisapplication.commandbars (_ "Worksheet Menu Bar")
MenuBarItem = mainmenubar.controls.add (_ office.msocontroltype.msocontrolpopup, temporary: = true) MenuBarItem.caption = CAPTION CATCH EX AS Exception MessageBox.show (ex.Message, _ _
EX.Source, MessageBoxButtons.ok, MessageBoxicon.Error
END TRY
End Sub
// c #
Private vidiniTmenUBarItems (String Caption)
{Try {MainMenuBar = ThisApplication.CommandBars [ "Worksheet Menu Bar"]; MenuBarItem = MainMenuBar.Controls.Add (Office.MsoControlType.msoControlPopup, Type.Missing, Type.Missing, Type.Missing, true); MenuBarItem.Caption = Caption }
Catch (Exception EX) {MessageBox.show (ex.Message, Ex. Source, MessageBoxButtons.OK, MessageBoxicon.Error);}}
3. Add the following code to the existing THISWORKBOOK_OPEN program, this code calls the INITMENUBARITEMS program you just created.
'Visual Basic
INITMENUBARITEMS ("& Custom Code")
// c #
INITMENUBARITEMS ("& Custom Code");
4. Select Save all files on the File menu to save the entire program.
5. Press F5 to run the project, load the Excel and your workbook.
6. In Excel, view the menu item to write the menu for the Custom Code to display the right side of the help menu. As shown in Figure 1: Figure 1: Excel with a custom menu bar project
Establish a menu item
With the right custom menu bar, you can join the new menu. Menu items are represented as a CommandBarControl object, and you will create a new CommandBarControl instance using the previously established menu bar project Controls collection Add method.
Establish a menu item
1. Add the following programs to the OfficeCodeBehind class, this program creates CommandBarControl and sets its title:
'Visual Basic
Private function cretebutton
BYVAL PARENT As Office.commandbarpopup, _
BYVAL CAPTION As String) As office.commandbarbutton
Try
DIM CBC as Office.commandbarControl
CBC = parent.controls.add (_ office.msocontroltype.msocontrolbutton, temporary: = true)
Cbc.caption = CAPTION
Cbc.visible = true
Return Directcast (CBC, Office.commandbarbutton)
Catch exception
Messagebox.show (ex.Message, _
Ex.Source, MessageBoxButtons.ok, MessageBoxicon.Error) End Try
END FUNCTION
// c #
Private Office.commandbarbutton CreateButton (Office.commandbarpopup Parent, String Caption)
{Office.commandbarcontrol CBC = NULL;
Try {cbc = parent.controls.add (office.msocontroltype.msocontrolbutton, type.missing, type.missing, type.missing, true); cbc.caption = caption; cbc.visible = true;
Catch (Exception EX)
{MessageBox.show (ex.Message, Ex. Source, MessageBoxButtons.ok, MessageBoxicon.Error);
Return (office.commandbarbutton) cbc;}
2. Add the following code to thisworkbook_open program, the following code calls the initMenuBarItems program:
'Visual Basic
Menuitem = CreateButton (_
Directcast (MenuBarItem, Office.commandbarpopup), _ "Run Demo Code")
// c #
Menuitem = CreateButton (office.commandbarpopup) MenuBarItem, "Run Demo Code");
3. Select Save all files on the File menu to save the entire program.
4. Press F5 to run this project, load an Excel and your workbook.
5. Click on the custom top-level menu in Excel to view the Run Demo Code menu item. As shown in Figure 2: Figure 2: After adding a menu item
Intercept menu items Click event
In order to complete this drill, you need to add an event to deal with the response after the custom menu item is clicked.
(Only in Visual Basic)
Intercept menu item click event
Complete the following steps in the Visual Basic .NET to add the event handler that the menu item is clicked.
Add event processing for custom menu items (Visual Basic)
1. Select MenuItem in the Class Name drop-down list in the upper left corner of the code editor.
2. Select Click in the Method Name drop-down list in the upper right corner of the code editor.
3. Modify the MenuItem_Click program, add the following code:
'Visual Basic
Messagebox.show (String.Format)
"You Just Clicked The Button Labled '{0}'. {1}" & _
"The name of Your Workbook is '{2}'.", _
Ctrl.caption, environment.newline, thisworkbook.name, _ "Menuitem_Click", MessageBoxButtons.ok, _ MessageBoxicon.information)
4. Select Save all files in the File menu to save the entire solution.
5. Press F5 to run this project, load the Excel and your workbook.
6. In Excel, click the Custom Code menu, then select Run Demo Code.
A warning box appears to display the current workbook.
(C #) Interception Click Menu Project Event
Complete the following steps in Visual C # to add event processing that click Custom Menu Bar Project.
Add event processing for custom menu items (C #)
1. Add the following programs in the OfficeCodeBehind class:
// C # private
void MenuItem_Click (Office.CommandBarButton Ctrl, ref Boolean CancelDefault) {MessageBox.Show (String.Format ( "You just clicked the button labeled '{0}' ./ n" "The name of your workbook is '{1}' ", Ctrl.caption, thisworkbook.name)," Menuitem_Click ", MessageBoxButtons.ok, MessageBoxicon.information;
2. Modify the thisworkbook_open program, add the following code:
// c #
Menuitem.click = new microsoft.office.core. _
Commandbarbuttonevents_clickeventhandler (MenuItem_click);
Tip: If you enter the first line of the code (until =), Visual Studio .NET will prompt you to press the TAB key. The code editor will insert the remaining code for you. This automatic new feature makes it easier to complete the event handler. Test this application
Now you can test your own custom menu item.
Test this application
1. Select the Save all files for the file menu to save the entire project.
2. Press F5 to run this project, load an Excel and your workbook.
3. A description of your current workbook appears in the warning box.
in conclusion
A core feature of Microsoft Office Application is the ability to create menus and toolbars. The Office CommandBar object provides a way from defining menus and toolbars. Although this exercise demonstrates how you add code to respond to the Click on the Excel menu, you will find behavior in Word and this similar.
