Once you have created command bar customizations, you usually have to do one of two things with them. Either you need to find them and reconnect their event procedures, or you need to find them and remove them. (You may need to find and remove command bar customizations when the user unloads an add-in or when the application is unloaded and the customizations are to be removed.) The best way to find a set of customizations is by using the FindControls method. However, there are actually two ways to find a customization. Either iterate through the controls on a specific menu or toolbar or use the FindControls method. So when should you use one technique over the other? The new FindControls method included in Office 2000 allows you to mimic built-in command bar control behavior.
What if there is more than one instance of your command on the command bar or somewhere else on some other menu or toolbar? The FindControls method allows you to find all instances of your control. The FindControls method thus handles the case in which the user has moved your custom command from its original position or has copied the custom command to another menu. Built-in controls could also be copied many times in a command bar set. For example, the built-in Save command is on both the File menu and the Standard toolbar, by default. Nonetheless, the FindControls method finds all instances of a built-in or custom control and can manipulate them accordingly, or assign them to an event procedure.
You can specify any combination of the four optional parameters of the FindControls method to narrow the control search. However, you'll usually use the Id or the Tag parameter. You should specify the Id parameter when searching for built-in controls. Each built-in command bar control, such as the Save or Open command, has a unique built-in identifier, or Id, in Office. When you're searching for all built-in controls with a specific Id, you should specify the Id parameter of the FindControls method only. When you're searching for custom controls, you should specify the Tag argument. (The Id property for all custom controls is 1.) As described near the beginning of this chapter in the section entitled "Setting the Tag Property," you should always set the Tag property for any command bar controls you add.
The following procedure finds all instances of the Save command and changes the caption from the default "Save" to "Save Document." You can also use the procedure to reset all built-in instances of a control. In the procedure, place a comment at the beginning of the line that sets the Caption property and remove the comment from the line below that resets the command bar control to its built-in state. In the Visual Basic Editor in any Office application, insert a standard code module, add the following code, and run the procedure.
Sub RenameBuiltInCommand()
Dim ctlItem As CommandBarControl
Dim ctlColl As CommandBarControls
Set ctlColl = Application.CommandBars.FindControls(Id:=3)
If Not ctlColl Is Nothing Then
For Each ctlItem In ctlColl
ctlItem.Caption = "&Save Document"
'ctlItem.Reset
Next ctlItem
End If
End Sub
|
TIP
To determine the Id of a built-in control quickly, execute a line like the following in the Immediate window in the Visual Basic Editor. Just change the name of the command bar where the control exists and the name of the control.
?Application.CommandBars("File").controls("&Save").Id
To reset the Save command to its default characteristics as defined by Office, remove the apostrophe (') from the beginning of the line ctlItem.Reset and add an apostrophe at the beginning of the line setting the Caption property.
The FindControls method, defined as FindControls([Type], [Id], [Tag], [Visible]), returns a CommandBarControls collection object. If no controls are found that fit the criteria specified by the arguments of the FindControls method, FindControls is set to the Visual Basic for Applications keyword Nothing. Don't confuse the new FindControls (plural) method with the FindControl (singular) method that has existed since Office 97.
The FindControl method works a lot like the new FindControls method in Office 2000. However, if the CommandBars collection contains two or more controls that fit the search criteria, the FindControl method returns only the first control that's found. Therefore, if the user customized the command bar set and copied your custom control to a new location, the FindControl method isn't robust enough to find all instances of your custom control. With the new FindControls method, if the CommandBars collection contains two or more controls that fit the search criteria, the FindControls method returns a CommandBarControls collection. Your code would then iterate through the CommandBarControls collection, manipulating each control in the collection as appropriate.
Sub InsertNewButton()
With Application.CommandBars("Standard") _
.Controls.Add(Type:=msoControlButton)
.Caption = "&Button1"
.FaceId = 2141
.Tag = "MyCustomTag"
End With
End Sub
|
A new custom button, with the FaceId set to 2141, is added to the end of the Standard toolbar.
You now have multiple copies of your custom button control on the command bar set.
This step will reveal that the FindControls method will find all instances of a custom control, regardless of what customizations have been made to it.
Sub DeleteControlUsingFindControls()
Dim ctlItem As CommandBarControl
Dim ctlColl As CommandBarControls
Set ctlColl = Application.CommandBars _
.FindControls(Tag:="MyCustomTag")
If Not ctlColl Is Nothing Then
For Each ctlItem In ctlColl
ctlItem.Delete
Next ctlItem
End If
End Sub
|
All instances of your custom button control that you copied to existing toolbars and the menu bar should have been removed. (The code in step 6 removes all instances of the custom command bar control you added in steps 1 and 3.) The search is conducted by using the FindControls method and specifying the Tag argument. Because in step 6 you set the Tag argument to the string "MyCustomTag," which is the same Tag value set in the InsertNewButton procedure, the If...Then loop in step 6 finds all instances.
When you create add-ins, as described in Chapters 13 and 14, you need a way to connect an event procedure to a custom command bar control that already may exist in the command bar set when the add-in is loaded. In the procedure that's called when an add-in is loaded, code should exist to find any custom controls that the add-in works with. If the custom control doesn't exist, you should add code to your add-in to recreate it. Otherwise, your code connects the custom control to its event procedure. The basic structure of your code would appear similar to the following procedure. However, this topic is described in full in Chapter 14.
Sub SomeAddInInitialization()
Dim ctlColl As CommandBarControls
Set ctlColl = Application.CommandBars _
.FindControls(Tag:="MyCustomTag")
If ctlColl Is Nothing Then
' Add command bar controls.
Else
' Reconnect controls to event procedures.
End If
End Sub
|
You assign the ctlColl variable in the Set statement to the collection returned by the FindControls method. You use the If…Then…Else block to determine if the collection is Nothing. If it is, it means that no custom controls with the tag specified exist and your code would call a procedure that adds the custom command bar controls. Otherwise, at least one copy of the custom control exists somewhere in the command bar set and the first item in the collection returned by the FindControls method is assigned to the button object that handles the events.
Sometimes you may not be concerned that a command bar set has multiple copies of a command bar control. Instead, you may be concerned with just one instance of a control on a specific command bar. When you iterate through the command bar controls collection on a command bar, you're not addressing the fact that the control you're interested in may have been copied or moved to another location, and that all your code is set up to address a control on a specific command bar only.
One way to rectify this would be, in the Visual Basic Editor in any Office application, to insert a standard code module, add the following code, and run the procedure below. The procedure iterates through the controls on the Tools menu. If the caption of a control in the Tools menu is set to the string "&MenuItem," the control is deleted.
Sub DeleteControlByIteration()
Dim ctlItem As CommandBarControl
For Each ctlItem In Application _
.CommandBars("Tools").Controls
If ctlItem.Caption = "&MenuItem" Then
ctlItem.Delete
End If
Next ctlItem
End Sub
|
However, iterating through a command bar controls collection to find a control by comparing the caption is not recommended. It can easily bring complications. For example, if you didn't have the accelerator in the Caption string, the correct control wouldn't be found. Also, the user may have changed the caption of the control when in command bar customize mode. The caption can also be specific to a locale, so your code would need to understand what locale the solution and Office are running in and what the locale-specific name of the control may be.
A better iteration approach involves using the Tag property to find a control in the command bar controls collection. This approach is safer and better because the user can't change the Tag property. In the procedure above, change the expression in the If…Then statement to evaluate the Tag property. This line would appear as If ctlItem.Tag = "MyCustomTag" Then.
In the section "Remove a Command Bar by Trapping Errors" earlier in this chapter, the procedure DeleteCommandBarByErrorTrapping attempts to delete a specific command bar. If the command bar does not exist, an error would occurs when the Delete method tries to delete a nonexistent command bar. Execution in the procedure then skips to the line starting with the word "Error_Handler." Similarly, the following procedure traps the error that's generated when a line of code attempts to delete a command bar that doesn't exist.
The first line in the procedure indicates that if an error occurs, execution in the procedure should skip to the line starting with the word "Error_Handler." (You can change the text "Error_Handler" to anything you want, as long as the colon (:) appears at the end of the line. If you do change the text, make sure to do so in both instances of it in the procedure.) If no error occurs when the Delete method is executed, the procedure is exited. Note that if the Exit Sub line didn't exist, execution of the procedure's lines of code would continue into the Error_Handler, even if no error occurred.
Sub DeleteControlByErrorTrapping()
On Error GoTo Error_Handler
Application.CommandBars("Tools") _
.FindControl(Tag:="MyButton").Delete
Exit Sub
Error_Handler:
MsgBox "The command button does not exist"
End Sub
|
In the Visual Basic Editor in any Office application, insert a standard code module, add the preceding code, and run the procedure. The procedure attempts to delete the control with the Tag "MyButton." This example uses the FindControl method, similar to the FindControls (plural) method to return the control that has the Tag assigned to the string "MyButton." If the control does not exist, the Delete method produces an error, and code execution skips to the line starting with the word "Error_Handler."