How To Use Automation to Create Office Command Bars and Controls with Visual Basic .NET

Article translations Article translations
Article ID: 303017 - View products that this article applies to.
This article was previously published under Q303017
Expand all | Collapse all

SUMMARY

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.

MORE INFORMATION

The Visual Basic .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 Visual Basic .NET Automation Client

  1. Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Under Project types click Visual Basic 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 theMicrosoft 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 INFO: Microsoft Office XP PIAs Are Available for Download
    3. Click OK in the Add References dialog box to accept your selections.
  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.vb:
    Imports Office = Microsoft.Office.Core
    Imports Excel = Microsoft.Office.Interop.Excel
    					
  5. In the code window, replace the following code
    Private Sub Button1_Click(ByVal sender As System.Object, _
                              ByVal e As System.EventArgs) _
                              Handles Button1.Click
    End Sub
    					
    with:
    ' Declare variables.
    Dim WithEvents oButton As Office.CommandBarButton
    Dim WithEvents oEdit As Office.CommandBarComboBox
    Dim WithEvents oDrop As Office.CommandBarComboBox
    Dim WithEvents oCombo As Office.CommandBarComboBox
    Dim WithEvents oPopupButton As Office.CommandBarButton
    
    
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' Declare variables.
        Dim oExcel As Excel.Application
        Dim oCommandBar As Office.CommandBar
        Dim oPopup As Office.CommandBarPopup
    
        ' Start Excel.
        oExcel = New Excel.Application()
        ' Show Excel and set UserControl.
        oExcel.Visible = True
        oExcel.UserControl = True
        ' Add a new workbook.
        oExcel.Workbooks.Add()
    
        ' Create a new command bar.
        oCommandBar = oExcel.CommandBars.Add("Billiards Sample")
    
        ' Add a button to the command bar.
        oButton = oCommandBar.Controls.Add(Office.MsoControlType.msoControlButton)
        ' Set the caption and face ID.
        oButton.Caption = "New game"
        oButton.FaceId = 1845
    
        ' Add an edit box to the command bar.
        oEdit = oCommandBar.Controls.Add(Office.MsoControlType.msoControlEdit)
        ' 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
    
        ' Add a combo box to the command bar.
        oCombo = oCommandBar.Controls.Add(Office.MsoControlType.msoControlComboBox)
        ' Add items to the combo box.
        oCombo.AddItem ("Sharky")
        oCombo.AddItem ("Cash")
        oCombo.AddItem ("Lucky")
        ' Set the caption and style.
        oCombo.Caption = "Choose your opponent:"
        oCombo.Style = Office.MsoComboStyle.msoComboLabel
    
        ' Add a drop-down list box to the command bar.
        oDrop = oCommandBar.Controls.Add(Office.MsoControlType.msoControlDropdown)
        ' Add items to the list box.
        oDrop.AddItem ("8 Ball")
        oDrop.AddItem ("9 Ball")
        oDrop.AddItem ("Straight Pool")
        oDrop.AddItem ("Bowlliards")
        oDrop.AddItem ("Snooker")
        ' 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
    
        ' Add a pop-up menu to the command bar.
        oPopup = oCommandBar.Controls.Add(Office.MsoControlType.msoControlPopup)
        ' 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 = oPopup.CommandBar.Controls.Add(Office.MsoControlType.msoControlButton)
        ' Change the face ID and caption for the button.
        oPopupButton.FaceId = 643
        oPopupButton.Caption = "Break!"
    
        ' Show the command bar to the user.
        oCommandBar.Visible = True
    End Sub
    
    Private Sub oButton_Click(ByVal Ctrl As Office.CommandBarButton, _
                              ByRef CancelDefault As Boolean) Handles oButton.Click
        ' Reset all values.
        oEdit.Text = ""
        oDrop.ListIndex = 1
        oCombo.Text = ""
        Console.WriteLine ("New game button clicked")
    End Sub
    
    Private Sub oCombo_Change(ByVal Ctrl As Office.CommandBarComboBox) Handles oCombo.Change
        Console.WriteLine ("oCombo_Change event fired -- New opponent = " + Ctrl.Text)
    End Sub
    
    Private Sub oDrop_Change(ByVal Ctrl As Office.CommandBarComboBox) Handles oDrop.Change
        Console.WriteLine ("oDrop_Change event fired -- Game type = " + Ctrl.Text)
    End Sub
    
    Private Sub oEdit_Change(ByVal Ctrl As Office.CommandBarComboBox) Handles oEdit.Change
        Console.WriteLine ("oEdit_Change event fired -- Player's name = " + Ctrl.Text)
    End Sub
    
    Private Sub oPopupButton_Click(ByVal Ctrl As Office.CommandBarButton, _
                                   ByRef CancelDefault As Boolean) Handles oPopupButton.Click
        Dim oRand As System.Random
    
        Console.WriteLine ("oPopupButton_Click event fired")
        ' Create a new random number class.
        oRand = New System.Random()
        Dim sWinner As String
        ' Get a random number and check its range.
        If (oRand.NextDouble() > 0.5) Then
            sWinner = oEdit.Text
        Else
            sWinner = oCombo.Text
        End If
        ' Show a message box to the user.
        MessageBox.Show("Game: " + oDrop.Text + vbCrLf + vbCrLf + "Name: " + oEdit.Text + _
                vbCrLf + "Opponent: " + oCombo.Text + _
                vbCrLf + vbCrLf + "Winner: " + sWinner, "Game Results")
    End Sub
    					
  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:
282830 PRB: Programmatic Access to Office XP VBA Project Is Denied

REFERENCES

For more information on Office Automation, see the following Microsoft Office Development Web site:
http://support.microsoft.com/ofd

Properties

Article ID: 303017 - Last Review: August 23, 2005 - Revision: 5.4
APPLIES TO
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
Keywords: 
kbautomation kbhowto KB303017

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com