To create a custom menu item in Excel

excel| Menu Walkthrough: Creating 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 objects provide a way to add menu items and toolbar button codes. In this walkthrough, you will create a menu bar under the custom menu item and add code to respond to the Office menu.

Content:

Introduced

Creating menus and toolbar items is a central feature of Microsoft Office, although the experiment is a demonstration of using these items in Microsoft Office Excel 2003, but these actions are similar in Office Word. (The difference is that in Word the menu name is bar, and in Excel it's called Worksheet menu bar.) You will create a menu item in the Excel main menu. Then, you add this menu item. Finally, you add the Click event code to execute the custom code.

Tip: The object module for Office menus and toolbars is defined in Office.dll, when you set up a new visual Studio tools for Microsoft Office System project, Microsoft Visual studio®. NET automatically contains references to this module.

Prerequisite

To complete this walkthrough, 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

Hint: If you are visual Basic. NET programmer, you need to set Option Strict to On (or you add Option Strict declarations in each module). Although this is not necessary, this ensures that you do not perform unsafe type conversions. In the future, the benefits of taking advantage of this option will be much greater than the difficulty of adding code.

Begin

You will build a new visual Studio. NET Excel project to begin with.

Establish a project

Use Microsoft Office System's visual Studio tool to create 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 Item.

2. On the project types panel, expand the Microsoft Office System project, and then select a visual Basic project or Visual C # project.

3. Select the Excel workbook in the template panel.

4. Named Excelcommandbars, then stored in the local hard drive.

5. Accept the default value in the Microsoft Office Project Wizard and click Finish.

Visual Studio. NET opens ThisWorkbook.vb or ThisWorkboo.cs files for you in the Code Editor.

Create a menu bar item

Creating a menu bar on an Excel main menu item requires you to add a CommandBarControl using the Add method.

Create a menu bar item in Excel

1. Add the following variables under the stored variables 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 program (established through the project template) in the Officecodebehing class that initializes the previously declared Mainmenubar and Menuitembar objects.

' Visual Basic 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 Catch ex as Exception MessageBox.Show (ex. Message, _

Ex. Source, MessageBoxButtons.OK, Messageboxicon.error)

End Try

End Sub

C#

private void Initmenubaritems (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, which 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 scheme.

5. Press F5 to run the project and load Excel and your workbook.

6. In Excel, view the menu bar item label with the custom code menu displayed on the right-hand side of the Help menu. As shown in Figure one:


Figure I: Excel with custom menu bar items

Create a menu item

With the appropriate custom menu bar, you can add a new menu. The menu item is represented as a CommandBarControl object, and you will use the Add method of the previously established menu bar item Controls collection to create a new CommandBarControl instance.

Create a menu item

1. Add the following procedure to the OfficeCodeBehind class, which establishes the CommandBarControl and sets its caption:

' Visual Basic

Private Function Createbutton (_

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 ex as 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 the 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 scheme.

4. Press F5 to run this project and load Excel and your workbook.

5. Click the Custom top-level menu in Excel to view the Run Demo Code menu item. As shown in figure II below:


Figure II: After adding a menu item



Click event to intercept menu item

To complete this walkthrough, you need to add an event to handle the response after the custom menu item is clicked.

(used in visual Basic only)

Intercept menu item Click event

In Visual Basic. NET in the following steps to increase the menu item is clicked event handler.

Increase event handling for custom menu items (Visual Basic)

1. In the upper-left corner of the Code Editor, select MenuItem in the class name Drop-down list.

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 to add the following code:

' Visual Basic

MessageBox.Show (String.Format (_

"You just clicked the button labeled ' {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 and load Excel and your workbook.

6. In Excel, click the Custom Code menu, and then select Run Demo code.

A warning box appears, displaying the current workbook.

(C #) Intercept Click menu item Events

Complete the following steps in Visual C # to increase the event handling for clicking on the custom menu bar item.

Increase event handling for custom menu items (C #)

1. Add the following procedure to 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 to add the following code:

C#

MenuItem.Click + = new Microsoft.Office.Core. _

Commandbarbuttonevents_clickeventhandler (Menuitem_click);

Tip: If you enter the first line of code (up to + =), Visual Studio. NET prompts you to press the TAB key. The Code Editor will insert the rest of the code for you in this row. This new feature of automatic completion makes it easier for you to complete an event handler.

Test this application

Now you can test your own custom menu items.

Test this application

1. Select the File menu to save all files to save the entire project.

2. Press F5 to run this project and load Excel and your workbook.

3. A description of your current workbook appears in the Warning box.

Conclusion

One of the core features of Microsoft Office applications is the ability to create menus and toolbars. The Office CommandBar object provides a way to customize menus and toolbars. Although this walkthrough demonstrates how you can add code to respond to an Excel menu click, you will find that the behavior in Word is similar.