Creating a command bar

In VBA, you create a new toolbar by using the Add method of the CommandBars collection. The following instruction creates a new toolbar with a default name, such as Custom 1. The created toolbar is initially empty (has no controls) and is not visible (its Visible property is False).


More often, you'll want to set some properties when you create a new toolbar. The following example demonstrates one way to do this:

Sub CreateAToolbar()

Dim TBar As CommandBar Set TBar = CommandBars.Add With TBar

The CreateAToolbar procedure uses the Add method of the CommandBars collection to add a new toolbar and create an object variable, Tbar, that represents this new toolbar. Subsequent instructions provide a name for the toolbar, set its position to the extreme upper-left corner of the screen, and make it visible. The Top and Left properties specify the position of the toolbar, and these settings represent screen coordinates — not Excel's window coordinates.

When you access the CommandBars collection in a code module for ThisWorkbook,you must precede the references with the Application object. For example:


You need to qualify the reference because the ThisWorkbook object has a CommandBars property. If your code is in a standard VBA module or in a code module for a UserForm,Sheet,or Chart, this is not necessary. You might expect the ThisWorkbook.CommandBars property to return a list of command bars attached to the workbook—but it doesn't. In other words, the CommandBars property of a Workbook object is completely useless.

0 0

Post a comment