Finding Commands


You can also use the FindControl method to return a CommandBarControl object.

This is only available on the CommandBars object.

If no controls are found that fit the criteria then "Nothing" is returned.
If this method find two or more controls that fit the search criteria, the first control that is found is returned.

CommandBars.FindControl(Type, Id, Tag, Visible, Recursive)

Type can be any of the above types
Id The identifier of the control. This identifies the action performed by the control.
Tag The tag value of the control to be searched for
VisibleFalseTrue to indicate that only visible controls are to be searched.
RecursiveFalseTrue to indicate the commandbar and all of its pop ups sub toolbars are to be included in the search.

The Recursive argument is only available when used on the CommandBar object.

Set objStandardControl = CommandBars("Standard").FindControl(Id:=100, Recursive:=True) 
Set objCommandBar = CommandBars("Standard").FindControl(Id:=100, Recursive:=True).CommandBar

Finding Built-in Commands

Every built-in command (inc menus) has a unique Id associated with it.
This is the best property to use when searching for built-in commands.

Finding Custom Commands

This method can be used to quickly locate controls on toolbars and menus.
This can be really useful when working with your custom toolbars and menus because you can assign a Tag property to all of these commands.
Assuming that all your commands have a unique Tag assigned to them this method will return the corresponding CommandBarControl is returned

Recursive Argument

This argument specifies if you want to recursively search the whole command bar including any drop-down menus and extension controls.
There are no controls passed the third level.
The default argument is False.

Public Sub SearchForID() 
Dim objcommandbar As CommandBar
Dim objcontrol As CommandBarControl
   For Each objcommandbar In CommandBars
   Set objcontrol = objcommandbar.FindControl(ID:=283, Recursive:=True)
   If Not (objcontrol Is Nothing) Then
      Debug.Print objcommandbar.Name
   End If
   Next objcommandbar
End Sub

Using the Tag property

Dim objCommandBar As Office.CommandBar 
Dim objControl As Office.CommandBarControl

Set objCommandBar = Application.CommandBars("Formatting")
Set objControl = objCommandBar.FindControl(Tag:="UniqueTag")

Moving Commands

Dim objControl As CommandBarControl 
Dim objControl2 As CommandBarControl
Dim objControl3 As CommandBarControl
Set objControl = Application.CommandBars("Cell").FindControl(ID:=3125)

moves the control to end

Set objControl2 = objControl.Move(CommandBars("Cell")) 
Set objControl3 = objControl2.Move(CommandBars("Cell"),7)

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext