Adding Customizations Similar to Built-In Controls

If you've used any of the Office applications, you've noticed a wide array of controls on the toolbars. Besides the basic buttons and menus, you see controls such as an edit combo box like the Zoom control and a split button pop-up like the Font Color control on the Formatting toolbar. You can use the Type property on the CommandBarControl object to determine the type of a control. The Type property returns an MsoControlType constant. The following table shows a summary of the types of controls you can create.

Control type	Control
msoControlButton
msoControlComboBox
msoControlDropdown
msoControlEdit
msoControlPopup

If the control type doesn't appear in the list, you unfortunately can't create it. For example, you can't create the control type msoControlSplitButton, even though it appears several times across the Office applications. You can't create the Font Color split button control (shown here) on the Formatting toolbar in Word, Excel, and PowerPoint, either.

In any menu or toolbar in the Office applications, a separator bar exists between both groups of menu items and groups of buttons. The separator bar is used to logically group menu items or toolbar buttons that are, in most cases, functionally related. For example, on the Edit menu, Cut, Copy, Paste, and Paste Special are grouped between two separator bars. You can display a separator bar between any two menu items or toolbar controls (including buttons, drop-down lists, edit controls, and so forth) by using the BeginGroup property. This property is found on the CommandBarButton, CommandBarCombobox, and CommandBarControl objects.

In the Visual Basic Editor in any Office application, click Module on the Insert menu, add the following code, and run the procedure. The procedure adds a new menu item to the File menu. The new menu item, with the caption "After Separator," is listed as the fourth item from the top in the File menu, and a separator bar is added before the item. For this procedure to work in Outlook, you need to add ActiveExplorer. before CommandBars in the following code:

Sub InsertControlAfterSeparator() With CommandBars("File").Controls .Item(4).BeginGroup = False With .Add(Before:=4) .Caption = "&After Separator" .BeginGroup = True End With End With End Sub

In Word, Excel, and PowerPoint, the File menu appears as seen in the following graphic.

You can set the BeginGroup property on any command bar control, no matter where it's located, unless the control is at the beginning of a toolbar, where the drag handles are, or at the top of a menu. Consequently, the separator bar is not a control but just a property of the control directly below it. If you wanted a new control to appear below a particular separator bar, you'd first have to do the following:

1. Set to False the BeginGroup property of the control directly under the current separator bar.
2. Add your custom control, as in the previous procedure.
3. Set the BeginGroup property to True on the control you added.

In this procedure, before the new menu item is added to the fourth position on the File menu, the BeginGroup property of the menu item that currently exists in the fourth position is set to False. In Word, Excel, and PowerPoint, the Save menu item exists in the fourth position on the File menu.

When you add and select different shapes from the Drawing toolbar in Word, Excel, and PowerPoint, both the caption of the last item on the Format menu in Word and PowerPoint and the first item on the Format menu in Excel change, depending on the type of shape selected. The caption cycles between Format Object, Format AutoShape, and Format Picture, among others. Similarly, depending on the selection, you may style the text contents of a selection to be bold, italics, or underline. If you apply one of these formats to the selection's text, the respective Bold, Italics, or Underline button on the Formatting toolbar in Word, Excel, and PowerPoint changes to a depressed state—that is, it toggles. The following procedure mimics these buttons by toggling the state of the button, as well as toggling the caption.

1. In the Visual Basic Editor in Word, Excel, or PowerPoint, insert a new class module and set its name to CmdBarToggleEvent in the Properties window. At the top of the class module, add the following declaration; add the procedure anywhere in the class module.

Public WithEvents CmdTgglCtrl As Office.CommandBarButton Private Sub CmdTgglCtrl_Click(ByVal Ctrl As _ Office.CommandBarButton, CancelDefault As Boolean) If Ctrl.State = msoButtonDown Then Ctrl.State = msoButtonUp Ctrl.Caption = "Button Up" ' do something when button is up Else Ctrl.State = msoButtonDown Ctrl.Caption = "Button Down" ' do something when button is depressed End If End Sub

2. Add the following procedure to a standard code module. Make sure the declaration below is added to the top of the standard code module.

Dim ToggleEvent As New CmdBarToggleEvent Sub InsertToggleButton() Dim ctlBtn As CommandBarButton Set ctlBtn = CommandBars("Standard") _ .Controls.Add(msoControlButton) With ctlBtn .Caption = "Button Up" .State = msoButtonUp .Tag = "tgglButton" .Style = msoButtonCaption End With Set ToggleEvent.CmdTgglCtrl = ctlBtn End Sub

3. Run the procedure InsertToggleButton, switch back to the application, and click the button with the caption "Button Up" on the Standard toolbar.

Keep clicking the button, and you'll see the state of the button toggle between the down and up states, while the caption also toggles between the strings "Button Up" and "Button Down."

If you don't want to do anything besides toggle the state of the control, you can just add the following line of code. This code uses the Visual Basic for Applications keyword Not to flip the value of the State property of the control. If the State property equals msoButtonDown, it has the integer value of -1. If the State value is msoButtonUp, it has the integer value of 0. The Not keyword will flip a value of 0 to a value of -1 (and vice-versa) and a value of True to a value of False (and vice-versa).

Ctrl.State = Not Ctrl.State

You also can create menu items that have a check mark next to them. You can do this only for menu items that don't have an icon image. Examples of built-in controls that have a check mark next to them when clicked are the Window menus in Word, Excel, and PowerPoint, and the Ruler command in Word. The steps and code to create a menu item with a check mark are identical to the steps and code in the previous section.

However, instead of adding the custom button control to the Standard toolbar, change the string "Standard" to "Tools." Click the custom control with the caption "Button Up" at the bottom of the Tools menu. The caption of the menu item will change and a check mark will appear and disappear as the menu item is clicked, as shown in the figure on the following page. The check mark is equivalent to the down state (msoButtonDown) of a button on a toolbar, and the absence of a check mark is equivalent to the up state (msoButtonUp) of a button on a toolbar.

You find a Zoom control on the Standard toolbar in Word, Excel, and PowerPoint, and you'll probably find it on most productivity software applications. The Zoom control lets the user enlarge or shrink the content on the screen. It also allows the user to choose between a list of predefined magnifications or to type in a custom magnification. In most applications that support a Zoom control, if you enter a magnification that the application can't support, the application will either display an alert indicating that the entered value is out of range, or just set the magnification to the closest supported value.

In Word, in Print Layout view, if you enter a value greater than 500% in the Zoom control on the Standard toolbar, you get an alert indicating that you can enter only values between 10% and 500%. In PowerPoint, on the other hand, if you specify a magnification value of greater than 400% (its upper limit), PowerPoint automatically sets the value of the control to the highest supported value (400%). At the other end of the scale, if the magnification value is smaller than the supported lower limit, PowerPoint sets the magnification to the lowest supported value (10%).

The following code inserts a combo box control at the end of the Standard toolbar. Six predefined values are added to the list in the combo box. If you add the control as type msoControlComboBox, the user can select from the list of items in the drop-down portion of the combo box, or enter a custom value in the edit portion of the combo box. The second procedure in this code handles the event that occurs when the value of the combo box changes.

1. In the Visual Basic Editor in Word, Excel, or PowerPoint, in a standard code module, add the following procedure:

Sub InsertComboBox() Dim ctlCombo As CommandBarComboBox Set ctlCombo = CommandBars("Standard") _ .Controls.Add(msoControlComboBox) With ctlCombo .AddItem "50%" .AddItem "60%" .AddItem "66%" .AddItem "75%" .AddItem "85%" .AddItem "100%" .Caption = "Size" .DropDownWidth = 52 .ListIndex = 3 .Visible = True .Width = 55 End With Set CmdBarEvents.ZoomCtrl = ctlCombo End Sub

The variable ctlCombo is declared as a CommandBarComboBox object, and the second line inserts the combo box control on the Standard toolbar in Word, Excel, or PowerPoint, depending on where you run the procedure. The AddItem method adds the specified string to the drop-down list of the combo box. Note that it's possible for you to insert an item at a specific position in the list by specifying the optional second argument of the AddItem method. For example, to insert 55% as the second item, enter the line of code .AddItem "55%", 2 anywhere in the With ctlCombo...End With section.

The Width property is the width of the combo box control that's visible on the toolbar. The DropDownWidth is the width of the drop-down list that appears when the user clicks the arrow at the right of the control.

2. Insert a new class module and in the Properties window set the name to CmdBarCtrlEvents.
3. At the top of the CmdBarCtrlEvents class module, add the following declaration:

Public WithEvents ZoomCtrl As _ Office.CommandBarComboBox

The ZoomCtrl variable is declared with events as a CommandBar-ComboBox. The use of the WithEvents keyword of the Visual Basic for Applications language in a class module indicates that the ZoomCtrl object variable will be used to respond to events triggered by the combo box. A CommandBarComboBox control in Office exposes only a Change event, and this is the event that's used to handle the event that occurs when the user has changed the value in the combo box.

4. When you entered the declaration with events in the previous step, the item ZoomCtrl was added to the Object drop-down list of the class module. If you select the ZoomCtrl item and then click the Procedure drop-down list, the Change event is the only event listed. Select the Change event and add the following code. (If you clicked the Change event in the Procedure drop-down list, you won't need the first two lines below; your action automatically inserted the Sub procedure definition for you.)

Private Sub ZoomCtrl_Change(ByVal Ctrl As _ Office.CommandBarComboBox) Static sCurValue As String, sEnteredText As String With Ctrl sEnteredText = .Text If Right$(sEnteredText, 1) = "%" Then sEnteredText = Left$(sEnteredText, _ Len(sEnteredText) - 1) End If If IsNumeric(sEnteredText) Then Select Case CInt(sEnteredText) Case Is > 125 .Text = "125%" Case Is < 10 .Text = "10%" Case Else .Text = sEnteredText & "%" End Select sCurValue = .Text ' call procedure that manipulates content ActiveWindow.View.Zoom = CLng(Left$( _ sCurValue, Len(sCurValue) - 1)) Else .Text = sCurValue End If End With End Sub

Office calls this procedure when the user changes the value of the combo box. When the event is triggered, Office passes into the Change event procedure an instance of the command bar combo box control where the event triggered. The first If…Then block removes the "%" sign from the text value of the combo box control, if it exists.

You use the built-in Visual Basic for Applications function IsNumeric to determine if the string entered by the user is a number. If it isn't, the text of the control is reset to the value that was there before the user changed it. If the text is a number, the procedure converts the value to an integer using the CInt function (built-in Visual Basic for Applications function), and then the procedure determines whether the value is above or below the specified bounds.

5. If you typed the code in Excel, before running the InsertComboBox procedure in step 1 change the line setting the Zoom property to the following:

ActiveWindow.Zoom = CLng(Left$(sCurValue, _ Len(sCurValue) - 1))

The only difference between this line in Excel and the same line for Word and PowerPoint is that the Zoom property is accessible from the Window object in Excel, while in Word and PowerPoint, the Zoom property is accessible on the View object.

6. At the top of the standard code module where the procedure in step 1 was added, insert the following line:

Dim CmdBarEvents As New CmdBarCtrlEvents

The variable CmdBarEvents declares a new instance of the class CmdBarCtrlEvents, which contains the event procedures for any command bar controls you've declared with events, as done in step 3 previously. This declaration also ties in the line Set CmdBarEvents .ZoomCtrl = ctlCombo added at the end of the procedure in step 1.

7. Run the procedure in step 1, switch to the application, click any item in the drop-down list of the newly inserted combo box (or enter a custom value) and press ENTER.

The code in the ZoomCtrl_Change procedure is executed and, depending on the value entered, the value is either displayed or set to the minimum or maximum supported value. Try entering values like 5 and 500 to see the execution of the code that handles limits.

The combo box in this example mimics the behavior of the built-in Zoom control on the Standard toolbar in Word, Excel, and PowerPoint in terms of how the combo box handles string versus numeric input, and whether the input is greater or less than a predefined value. The line of the ZoomCtrl_Change procedure that assigns the value to the Zoom property, ActiveWindow.View.Zoom, actually changes the zoom in the active window in Word and PowerPoint. If you modified the code for Excel in step 5, the zoom in the active window in Excel will be changed similarly. (The built-in Visual Basic for Applications function CLng, used in steps 4 and 5, converts the string value in the variable sCurValue to a value of type Long.)

A noneditable drop-down list control looks the same as a combo box control except for the fact that you can't type a value in the control. The user must select an item in the drop-down list and can't enter a custom value in the control, as can be done in a combo box control.

1. Add the following procedure to a standard code module. Make sure the declaration below is added to the top of the standard code module.

Dim CmdCombo As New CmdBarDropdownEvents Sub InsertDropDown() Dim ctlCombo As CommandBarComboBox Set ctlCombo = CommandBars("Standard") _ .Controls.Add(msoControlDropdown) With ctlCombo .BeginGroup = True .AddItem "Item 1" .AddItem "Item 2" .AddItem "Item 3" .AddItem "Item 4" .Caption = "List Items" .DropDownWidth = 117 .ListIndex = 3 .Width = 120 .Tag = "ItemList" End With Set CmdCombo.CmdListCtrl = ctlCombo End Sub

2. Create a new class module and name it CmdBarDropdownEvents. At the top of the class module, add the following declaration and then add the procedure anywhere in the class module.

Public WithEvents CmdListCtrl As _ Office.CommandBarComboBox Private Sub CmdListCtrl_Change(ByVal Ctrl As _ Office.CommandBarComboBox) Dim sListItemText As String Dim iPosnInList As Integer With Ctrl sListItemText = .List(.ListIndex) iPosnInList = .ListIndex Select Case sListItemText Case "Item 1" Debug.Print "1" Case "Item 2" Debug.Print "2" Case "Item 3" Debug.Print "3" Case "Item 4" Debug.Print "4" End Select End With End Sub

The code can do two things to evaluate what the selected item is in the drop-down list. The first is to evaluate the string of the selected item; the second is to evaluate the position of the selected item in the drop-down list. In either case, you use a Select Case statement to evaluate the property you've chosen. In the previous procedure, for example, you can use the sListItemText or iPosnInList variables in the Select Case statement as expressions to evaluate the selected item.

You use the ListIndex property of the CommandBarComboBox object to return the numeric position of the selected item. You also use this property when retrieving the string of the selected item. You do this former evaluation by using the List property of the CommandBarComboBox object and passing in an integer value in the List property. The integer value represents the position of the item in the drop-down list.

Because you are interested in retrieving the selected item and the ListIndex property returns the position of the selected item in the list, you pass the value of ListIndex directly into the List property. Note the use of the With…End block. The expression in the With…End block is the CommandBarComboBox control, so .List(.ListIndx) really represents Ctrl.List(Ctrl.ListIndex).

3. Display the Immediate window in the Visual Basic Editor.
4. Run the InsertDropDown procedure added in the first step, switch back to the application, and click the drop-down list.

To see the values being printed to the Immediate window in the Visual Basic Editor, position the Office application so that the Immediate window is visible behind the application window.

Unfortunately there's no simple way to assign an image to a toolbar button or menu item. There are, however, a number of workarounds, all of which have one thing in common: you need to get an image onto the clipboard. Once it's there, you use the CommandBarControl.CopyFace method to copy the image from the clipboard to the control. The crux lies in somehow copying the image to the Windows clipboard before using the CopyFace method.

If you plan on creating an add-in for any Office application, Chapters 13 and 14 describe how to do so using the new COM add-in model introduced in Office 2000. Microsoft Visual Basic 5.0 and newer versions allow developers who create COM add-ins to use a resource file and directly store images for command bar controls in a COM add-in.

In Visual Basic 6.0, for example, you can use the function LoadResPicture to retrieve an image resource from a resource file stored in your COM add-in. Thereafter, you can use the Clipboard object to set the image onto the Windows clipboard. The online Microsoft Visual Basic Help file describes the LoadRes-Picture and Clipboard functions as well as how to insert a resource file into your project. Your code would look similar to the following. The value of "100" used in the LoadResPicture function would represent an image in a resource file stored in your project.

With CommandBars("Tools").Controls.Add .Caption = "&MenuItem With Image" .Style = msoButtonIconAndCaption Clipboard.SetData LoadResPicture("100", vbResBitmap) .PasteFace End With

Often when the user clicks a menu item or toolbar button, code is executed that will retrieve data from a data source (rather than display a dialog box) and then format the data and insert it into the active document. This process may take a while, depending on whether the data source is local or on the network and on how much data is being retrieved. In these scenarios, you may want to change the cursor to an hourglass (or the equivalent wait pointer) on the user's system. This tells the user that something is happening behind the scenes and, when the cursor changes back to the normal selection pointer, the process is complete.

1. Add the following code to a standard code module in any Office application. In Access, change "Standard" to "Database." Also, in Access you must load the Microsoft Office 9.0 Object Library, using the References command on the Tools menu, since it is not loaded by default.

Dim CmdBarEvents As New Class1 Sub InsertButton() Dim ctlBtn As Office.CommandBarButton Set ctlBtn = CommandBars("Standard").Controls.Add With ctlBtn .Caption = "&Custom Button" .FaceId = 2950 .Tag = "BrButton" Set CmdBarEvents.BtnCtrl = ctlBtn End With End Sub

2. Add the following code in a class module named Class1. All lines before the BtnCtrl_Click procedure must be placed at the top of the class module.

Private Declare Function SetCursor Lib "User32" _ (ByVal hCursor As Long) As Long Private Declare Function LoadCursor Lib "User32" _ Alias "LoadCursorA" (ByVal hInstance As Long, _ ByVal lpCursorName As Any) As Long Private Declare Sub Sleep Lib "kernel32" ( _ ByVal dwMilliseconds As Long) Const IDC_WAIT As Long = 32514 Public WithEvents BtnCtrl As Office.CommandBarButton Private Sub BtnCtrl_Click(ByVal Ctrl As _ Office.CommandBarButton, CancelDefault As Boolean) Dim Start As Long SetCursor LoadCursor(0, IDC_WAIT) ' get current time and loop for 1 second Start = Timer Do While Timer < Start + 1 Loop ' use Sleep Windows API to sleep for 1 second more Sleep 1000 ' do something here SetCursor LoadCursor(0, 0&) End Sub

The Click event procedure uses the SetCursor function declared at the top of the class module to set the cursor to the wait pointer. You then use the Do…Loop to simulate the delay of some process, like retrieving data. The execution loops through the Do…Loop for one second and then exits the loop. After the Do…Loop, a value of 1000 is passed to the Sleep function. Since a thousand milliseconds equals one second, the code does not continue to the next line until one second has passed. The SetCursor function then resets the mouse pointer to the default pointer.

The three declarations, SetCursor, LoadCursor, and Sleep, are Windows application programming interfaces (or Win32 APIs). You can find more information about Win32 APIs at http://msdn.microsoft.com/ or any other Microsoft Developer Network source.

3. Run the InsertButton procedure added in the first step, switch back to the application, and click the button with the caption "Custom Button" at the end of the Standard toolbar.

When you click the button, the cursor changes to an hourglass for two seconds before changing back to the default cursor. When you add this code to your add-in, instead of adding code that pauses execution for two seconds, you could, for example, add code that retrieves data from a database.