Article ID: 166101 - Last Review: November 23, 2006 - Revision: 2.2

XL97: Visible Property Does Not Apply to All CommandBars

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q166101
For a Microsoft Excel 2002 version of this article, see 291068  (http://support.microsoft.com/kb/291068/EN-US/ ) .
For a Microsoft Excel 2000 version of this article, see 213716  (http://support.microsoft.com/kb/213716/EN-US/ ) .
Expand all | Collapse all

SYMPTOMS

When you attempt to hide or show a commandbar in a Visual Basic for Applications macro you may receive the following error message:
Run-time error '-2147467259 (80004005)':
Method 'Visible' of object 'CommandBar' failed
Back to the top

CAUSE

This problem occurs if you attempt to hide or show a Commandbar to which the Visible property does not apply.
Back to the top

WORKAROUND

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 Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (http://support.microsoft.com/gp/advisoryservice)

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS (http://support.microsoft.com/default.aspx?scid=fh;en-us;cntactms) The following example loops through all of the CommandBars and displays the CommandBar name in a message box, without generating the error message mentioned earlier.

NOTE: The "MsgBox ComBar.Name" line in the macro below is for demonstration purposes. There are over 80 CommandBars that use the Visible property, so you will receive over 80 message boxes. Remove this line if the macro is taking too long to run. 
   Sub Command_Bars()
       On Error Resume Next
       For Each ComBar In Application.CommandBars
           ComBar.Visible = False
           MsgBox ComBar.Name
       Next ComBar
   End Sub
An On Error statement was inserted before the For Each loop. If you run the macro without the On Error statement, you receive the error message mentioned earlier.
Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Back to the top

MORE INFORMATION

Not all of the CommandBar items support the Visible property. The following list contains CommandBar items that are known to generate the error message mentioned earlier, when used with the Visible property. 
   Index   CommandBar
   --------------------------------
   19      Query and Pivot
   20      Workbook tabs
   21      Cell
   22      Column
   23      Row
   24      Cell
   25      Column
   26      Row
   27      Ply
   28      XLM Cell
   29      Document
   30      Desktop
   31      Nondefault Drag and Drop
   32      AutoFill
   33      Button
   34      Dialog
   35      Series
   36      Plot Area
   37      Floor and Walls
   38      Trendline
   39      Chart
   40      Formula Bar
   41      PivotTable Context Menu
   42      Query
   43      Query Layout
   44      AutoCalculate
   45      Object/Plot
   46      Title Bar (Charting)
   47      Layout
   70      Shapes
   71      Inactive Chart
   72      Excel Control
   73      Curve
   74      Curve Node
   75      Curve Segment
   76      Pictures Context Menu
   77      OLE Object
   78      ActiveX Control
   79      WordArt Context Menu
   80      Rotate Mode
   81      Connector
   82      &Wizard
   83      Add Command
   84      Built-in Menus
   85      System
These CommandBars are used for shortcut menus and items such as the current list of sheets in a workbook.
Back to the top
APPLIES TO
  • Microsoft Excel 97 Standard Edition
Back to the top
Keywords: 
kberrmsg kbprogramming KB166101
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations