OFF2000: CommandBar Indexes Different fromOffice 97

• Microsoft Office 2000
• Microsoft Excel 2000
• Microsoft Access 2000
• Microsoft PowerPoint 2000
• Microsoft Word 2000

SYMPTOMS

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:

Run-time error '5':
Invalid procedure call or argument

CAUSE

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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

-or-

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

RESOLUTION

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.

MORE INFORMATION

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
 

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

Wi&zard              82            &Wizard 

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

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

REFERENCES

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.

Additional query words: XL2000 indices OFF2000

Keywords : kbdta kbdtacode OffVBA KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb