OFF2000: CommandBar Indexes Different from Office 97

When you run a Visual Basic macro that adds or manipulates toolbar buttons or menu commands for the CommandBars object, the controls may be added to a different CommandBar, or an error message similar to the following may appear:

This problem will occur when you use the CommandBars object and refer to a the control index rather than by name. For example, this problem will ocur if you refer to the index for the worksheet shortcut menu instead of its name "Cell."

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. When the following examples are run in Excel, you will receive the error mentioned in the Symptoms section.

Sub NewCommand()
   Set x = CommandBars(21).Controls.Add(Type:=msoControlButton)
   x.Caption = "hello"
End Sub
				

Sub ChangeCaption()
   CommandBars(21).Controls.Caption = "Hello world"
End Sub
				

To successfully work with the CommandBars object and its controls between Office 97 and Office 2000 programs, you must reference the CommandBar controls by their name instead of by their index numbers. See the "More Information" section for a list of Office 97 index numbers and their respective names in Office 2000.

The following table lists CommandBar indices for Office 97 and their CommandBar name in Office 2000.

Office 2000                Office 97
CommandBar name            index 
---------------            ---------

Worksheet Menu Bar          1
Chart Menu Bar              2
Standard                    3
Formatting                  4
PivotTable                  5
Chart                       6
Reviewing                   7
Forms                       8
Stop Recording              9
External Data              10
Auditing                   11
Full Screen                12
Circular Reference         13
Visual Basic               14
Web                        15
Control Toolbox            16
Exit Design Mode           17
Drawing                    18
Query and Pivot            19
Workbook tabs              20
Cell                       21
Column                     22
Row                        23
Cell                       24
Column                     25
Row                        26
Ply                        27
XLM Cell                   28
Document                   29
Desktop                    30
Nondefault Drag and Drop   31
AutoFill                   32
Button                     33
Dialog                     34
Series                     35
Plot Area                  36
Floor and Walls            37
Trendline                  38
Chart                      39
Formula Bar                40
PivotTable Context Menu    41
Query                      42
Query Layout               43
AutoCalculate              44
Object/Plot                45
Title Bar (Charting)       46
Layout                     47
WordArt                    48
Picture                    49
Shadow Settings            50
3-D Settings               51
Borders                    52
Chart Type                 53
Pattern                    54
Font Color                 55
Fill Color                 56
Line Color                 57
Order                      58
Nudge                      59
Align or Distribute        60
Rotate or Flip             61
Lines                      62
Connectors                 63
AutoShapes                 64
Callouts                   65
Flowchart                  66
Block Arrows               67
Stars & Banners            68
Basic Shapes               69
Shapes                     70
Inactive Chart             71
Excel Control              72
Curve                      73
Curve Node                 74
Curve Segment              75
Pictures Context Menu      76
OLE Object                 77
ActiveX Control            78
WordArt Context Menu       79
Rotate Mode                80
Connector                  81
&Wizard                    82
Add Command                83
Built-in Menus             84
System                     85

				

The following CommandBar in Office 97 has changed its name in Office 2000:

                           
Office 97            Office 97     Office 2000
CommandBar name      index         CommandBar name
---------------      ---------     ---------------

Wi&zard              82            &Wizard
				

The following CommandBars are new in Office 2000:

                         
CommandBar name 
--------------- 

Refresh    
EuroPlaceholder_2000
PivotChart Menu  
Format Data Series 
Format Axis   
Format Legend Entry 
Pivot Chart Popup 
Phonetic Information  
Script Anchor Popup 
Clipboard   
				

For more information about CommandBars, click Microsoft Excel Help on the Help menu, type CommandBar Object in the Office Assistant or the Answer Wizard, and then click Search to view the topic.