BUG: Changes Made to Excel CommandBars Through Automation Are Not Saved

• Microsoft Excel 97 for Windows
• Microsoft Excel 2000

SYMPTOMS

When you make changes to the Microsoft Excel CommandBars through Automation, the changes are applicable only in the instance of Excel that is being automated. The changes made do not appear in later instances of Excel.

CAUSE

Excel does not save menu and toolbar changes when being automated.

RESOLUTION

If all Automation references are released before Excel is shut down, Excel assumes it is exiting per instruction from the end user, and any CommandBar changes are saved for the current user. See the example below for a demonstration of how this can be accomplished programmatically.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start Excel.



2. Click Toolbars on the View Menu and then choose Customize.



3. Click on the New button in the Customize dialog box to add a new CommandBar.



4. Type ExistingBar for the Toolbar name and click OK.



5. Close the Customize dialog box and quit Excel.



6. Open a new Visual Basic EXE project. Form1 is created by default.



7. Click References on the Project menu and check Microsoft Visual Basic for Applications Extensibility 5.3, Microsoft Excel 9.0 Object Library (or Microsoft Excel 8.0 Object Library for Microsoft Excel 97) and Microsoft Office 9.0 Object Library (or Microsoft Office 8.0 Object Library for Microsoft Office 97).



8. Add a CommandButton to Form1. In the code module for Form1, add the following code:




Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

Private Sub Command1_Click()
  ' Start Excel:
    Set xlApp = CreateObject("Excel.Application")
    
  ' Add a new Workbook
    Set xlBook = xlApp.Workbooks.Add
    
    xlApp.Visible = True
    xlApp.UserControl = True

  ' ** Create a new toolbar with a dummy button**
  ' Add a new toolbar...
    Dim cbs As Office.CommandBars
    Dim cb As Office.CommandBar
    Set cbs = xlApp.CommandBars
    
  ' Note the Temporary argument is set to False.
    Set cb = cbs.Add("AddedBar", 1, , False)  '1=msoBarTop
    
  ' Make it visible and add a button...
    cb.Visible = True
    
    Dim cbc As Office.CommandBarControl
    Set cbc = cb.Controls.Add(1)   '1=msoControlButton
           
  ' Set text for the button
    cbc.Caption = "Dummy Button"
    cbc.FaceId = 2950     'Smiley
      
  ' Delete the Existing Custom Bar
    xlApp.CommandBars("ExistingBar").Delete
    xlApp.WindowState = xlMaximized
    Form1.WindowState = vbMinimized

    Dim sMsg As String
    sMsg = "Notice that the ExistingBar is deleted and AddedBar is added."
    sMsg = sMsg & vbCrLF & "Hit OK to continue"
    MsgBox  sMsg, vbMsgBoxSetForeground, "Pausing to view changes"
   
  ' Close the Workbook without saving changes and quit Excel.
    xlBook.Close False
    xlApp.Quit

  ' Remember to release references.
    Set xlBook = Nothing
    Set xlApp = Nothing
    Unload Me
End Sub 

9. Run the project and click the button.



10. When the message is displayed, you can see that the "ExistingBar" CommandBar is deleted and our new CommandBar "AddedBar" is displayed.



11. Click OK and let the program Quit Excel and terminate.



12. Now start Excel, and note that the added CommandBar is no longer there and the deleted bar still exists.

Workaround

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

Private Sub Command1_Click()
  ' Start Excel:
    Set xlApp = CreateObject("Excel.Application")
    
  ' Add a new Workbook.
    Set xlBook = xlApp.Workbooks.Add
   
    xlApp.Visible = True
    xlApp.UserControl = True
    
  ' Add a module to insert the macros:
    Dim xlmodule As VBIDE.VBComponent 'Object
    Set xlmodule = xlBook.VBProject.VBComponents.Add(vbext_ct_StdModule)
    
  ' Add a macro to the module...
    Dim strCode As String
    strCode = _
    "sub QuitPrep()" & vbCr & _
    "  application.ontime now() + timevalue(""00:00:01""),""DoQuit"" " _
    & vbCr & _
    "end sub" & vbCr & _
    "sub DoQuit()" & vbCr & _
    "  Application.ActiveWorkbook.Saved = True" & vbCr & _
    "  Application.Quit" & vbCr & _
    "end sub"
    xlmodule.CodeModule.AddFromString strCode

  ' ** Create a new toolbar with a button**
  ' Add a new toolbar...
    Dim cbs As Office.CommandBars
    Dim cb As Office.CommandBar
    Set cbs = xlApp.CommandBars
    
  ' note the Temporary argument is set to False.
    Set cb = cbs.Add("AddedBar", 1, , False)  '1=msoBarTop
    
  ' Make it visible and add a button...
    cb.Visible = True
    
    Dim cbc As Office.CommandBarControl
    Set cbc = cb.Controls.Add(1)   '1=msoControlButton
    
  ' Set text for the button.
    cbc.Caption = "Dummy Button"
    cbc.FaceId = 2950     'Smiley
    
  ' Delete the Existing Custom Bar.
    xlApp.CommandBars("ExistingBar").Delete
    xlApp.WindowState = xlMaximized
    Form1.WindowState = vbMinimized

    Dim sMsg As String
    sMsg = "Notice that the ExistingBar is deleted and AddedBar is added."
    sMsg = sMsg & vbCrLF & "Hit OK to continue"
    MsgBox sMsg, vbMsgBoxSetForeground, "See Changes"
    
  ' Call Quit Method via macro.
    xlApp.Run "QuitPrep"
    
  ' Remember to release references.
    Set xlBook = Nothing
    Set xlApp = Nothing
    Unload Me
End Sub
 

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Harsha Bennur, Microsoft Corporation

REFERENCES

For additional information on adding a VBA macro dynamically, click the article number below to view the article in the Microsoft Knowledge Base:

Q194611 HOWTO: Create and Call an Excel Macro Programmatically From VB

Additional query words: automation commandbar

Keywords : kbActiveX kbAutomation kbExcel kbVBp600 kbGrpDSO kbExcel97 kbDSupport kbexcel2000
Version : WINDOWS:2000,97
Platform : WINDOWS
Issue type : kbbug