HOWTO: Use Automation to Create Office Command Bars and Controls with Visual C# .NET

This article demonstrates how to automate Excel to create a command bar that contains buttons, drop-down list boxes, combo boxes, and pop-up menus.

The Visual C# .NET application catches and responds to the Click and Change events that the various command bar controls fire. Although this sample uses Excel as the host application, the command bar code will work in each of the Office applications.

Create the C# .NET Automation Client

1. Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Under Project types click Visual C# Projects, then click Windows Application under Templates. Form1 is created by default.
2. Add a reference to the Microsoft Excel Object Library and the Microsoft Office Object Library. To do this, follow these steps:
   1. On the Project menu, click Add Reference.
   2. On the COM tab, locate the Microsoft Excel Object Library and click Select.
      
      Note: Microsoft Office 2003 includes Primary Interop Assemblies (PIAs). Microsoft Office XP does not include PIAs, but they may be downloaded. For additional information about Office XP PIAs, click the article number below to view the article in the Microsoft Knowledge Base:
      328912  (http://support.microsoft.com/kb/328912/EN-US/ ) INFO: Microsoft Office XP PIAs Are Available for Download
   3. Click OK in the Add References dialog box to accept your selection.
3. On the View menu, click Toolbox to display the toolbox and add a button to Form1.
4. Double-click Button1. The code window opens at the onClick event for Button1. Add the following to the top of Form1.cs:

   using Office = Microsoft.Office.Core;
   using Excel = Microsoft.Office.Interop.Excel;
   					

5. In the code window, replace the following code

   private void button1_Click(object sender, System.EventArgs e)
   {
   }
   					

   with:

   // Declare variables.
   Office.CommandBarButton oButton;
   Office.CommandBarComboBox oEdit;
   Office.CommandBarComboBox oDrop;
   Office.CommandBarComboBox oCombo; 
   Office.CommandBarButton oPopupButton;
   
   private void button1_Click(object sender, System.EventArgs e)
   {
   	// Declare variables.
   	Excel.Application oExcel;
   	Office.CommandBar oCommandBar;
   	Office.CommandBarPopup oPopup;
   	Object oMissing = System.Reflection.Missing.Value;
   
   	// Start Excel.
   	oExcel = new Excel.Application();
   	// Show Excel and set UserControl
   	oExcel.Visible = true;
   	oExcel.UserControl = true;
   	// Add a new workbook
   	oExcel.Workbooks.Add(oMissing);
   
   	// Create a new command bar.
   	oCommandBar = oExcel.CommandBars.Add("Billiards Sample",oMissing,oMissing,
   
   	// Add a button to the command bar.
   	oButton = (Office.CommandBarButton)oCommandBar.Controls.Add(
   		Office.MsoControlType.msoControlButton,oMissing,oMissing,oMissing,oMissing);
   	// Set the caption and face ID.
   	oButton.Caption = "New game";
   	oButton.FaceId = 1845;
   	// Set up a delegate for the Click event.
   	Office._CommandBarButtonEvents_ClickEventHandler oButtonHandler = 
   			new Office._CommandBarButtonEvents_ClickEventHandler(oButton_Click);
   	oButton.Click += oButtonHandler;
   
   	// Add an edit box to the command bar.
   	oEdit = (Office.CommandBarComboBox) oCommandBar.Controls.Add(
   		Office.MsoControlType.msoControlEdit,oMissing,oMissing,oMissing,oMissing);
   	// Show a vertical separator.
   	oEdit.BeginGroup = true;
   	// Clear the text and show a caption.
   	oEdit.Text = "";
   	oEdit.Caption = "Enter your name:";
   	oEdit.Style = Office.MsoComboStyle.msoComboLabel;
   	// Set up a delegate for the Change event.
   	Office._CommandBarComboBoxEvents_ChangeEventHandler oEditHandler = 
   			new Office._CommandBarComboBoxEvents_ChangeEventHandler(oEdit_Change);
   	oEdit.Change += oEditHandler;
   
   	// Add a combo box to the command bar.
   	oCombo = (Office.CommandBarComboBox) oCommandBar.Controls.Add(
   		Office.MsoControlType.msoControlComboBox,oMissing,oMissing,oMissing,oMissing);
   	// Add items to the combo box.
   	oCombo.AddItem("Sharky",oMissing);
   	oCombo.AddItem("Cash",oMissing);
   	oCombo.AddItem("Lucky",oMissing);
   	// Set the caption and style.
   	oCombo.Caption = "Choose your opponent:";
   	oCombo.Style = Office.MsoComboStyle.msoComboLabel;
   	// Set up a delegate for the Change event.
   
   	Office._CommandBarComboBoxEvents_ChangeEventHandler oComboHandler = 
   			new Office._CommandBarComboBoxEvents_ChangeEventHandler(oCombo_Change);
   	oCombo.Change += oComboHandler;
   
   	// Add a drop-down list box to the command bar.
   	oDrop = (Office.CommandBarComboBox) oCommandBar.Controls.Add(
   			Office.MsoControlType.msoControlDropdown,oMissing,oMissing,oMissing,oMissing);
   	// Add items to the list box.
   	oDrop.AddItem("8 Ball",oMissing);
   	oDrop.AddItem("9 Ball",oMissing);
   	oDrop.AddItem("Straight Pool",oMissing);
   	oDrop.AddItem("Bowlliards",oMissing);
   	oDrop.AddItem("Snooker",oMissing);
   	// Set the value to the first in the list.
   	oDrop.ListIndex = 1;
   	// Set the caption and style.
   	oDrop.Caption = "Choose your game:";
   	oDrop.Style = Office.MsoComboStyle.msoComboLabel;
   	// Set up a delegate for the Change event.
   	Office._CommandBarComboBoxEvents_ChangeEventHandler oDropHandler = 
   			new Office._CommandBarComboBoxEvents_ChangeEventHandler(oDrop_Change);
   	oDrop.Change += oDropHandler;
   
   	// Add a pop-up menu to the command bar.
   	oPopup = (Office.CommandBarPopup) oCommandBar.Controls.Add(
   			Office.MsoControlType.msoControlPopup,oMissing,oMissing,oMissing,oMissing);
   	// Add a separator before the pop-up button.
   	oPopup.BeginGroup = true;
   	// Set the caption.
   	oPopup.Caption = "Rack 'em Up!";
   	// Add a button to the pop-up.
   	oPopupButton = (Office.CommandBarButton) oPopup.CommandBar.Controls.Add(
   			Office.MsoControlType.msoControlButton,oMissing,oMissing,oMissing,oMissing);
   	// Change the face ID and caption for the button.
   	oPopupButton.FaceId = 643;
   	oPopupButton.Caption = "Break!";
   	// Set up a delegate for the Click event.
   	Office._CommandBarButtonEvents_ClickEventHandler oPopupButtonHandler = 
   			new Office._CommandBarButtonEvents_ClickEventHandler(oPopupButton_Click);
   	oPopupButton.Click += oPopupButtonHandler;
   
   	// Show the command bar to the user.
   	oCommandBar.Visible = true;
   }
   
   private void oButton_Click(Office.CommandBarButton Ctrl, ref bool Cancel)
   {
   	// Reset all values.
   	oEdit.Text = "";
   	oDrop.ListIndex = 1;
   	oCombo.Text = "";
   	Console.WriteLine("New game button clicked");
   }
   
   private void oEdit_Change(Office.CommandBarComboBox Ctrl)
   {
   	Console.WriteLine("oEdit_Change event fired -- Player's name = " + Ctrl.Text);
   }
   
   private void oCombo_Change(Office.CommandBarComboBox Ctrl)
   {
   	Console.WriteLine("oCombo_Change event fired -- New opponent = " + Ctrl.Text);
   }
   
   private void oDrop_Change(Office.CommandBarComboBox Ctrl)
   {
   	Console.WriteLine("oDrop_Change event fired -- Game type = " + Ctrl.Text);
   }
   
   private void oPopupButton_Click(Office.CommandBarButton Ctrl, ref bool Cancel)
   {
   	System.Random oRand;
   
   	Console.WriteLine("oPopupButton_Click event fired");
   	// Create a new random number class.
   	oRand = new System.Random();
   	String sWinner;
   	// Get a random number and check its range.
   	if (oRand.NextDouble() > 0.5) 
   		sWinner = oEdit.Text;
   	else
   		sWinner = oCombo.Text;
   	// Show a message box to the user.
   	MessageBox.Show("Game: " + oDrop.Text + "\r\n\r\nName: " + oEdit.Text + 
   		"\r\nOpponent: " + oCombo.Text + "\r\n\r\nWinner: " + sWinner,"Game Results");
   } 
   
   					

6. Press F5 to build and run the program.
7. Click Button1 to start Excel, insert a new command bar, and insert controls on that command bar.

Additional Notes for Office XP

Office XP applications have a security option to allow programmatic access to the Visual Basic for Applications (VBA) object model. If this setting is "off" (the default), you may receive an error when you run the sample code. For additional information on this setting and how to correct the error, click the article number below to view the article in the Microsoft Knowledge Base:

For more information on Office Automation, see the following Microsoft Office Development Web site: