FIX: Changes Made to Excel CommandBars Through Automation Are Not Saved (241652)

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

To work around this problem, you could still make changes to the CommandBars through Automation, but instead of quitting Excel through Automation, you can release your reference to Excel and then call Quit in Excel through an asynchronous VBA macro.

To illustrate, replace the code in the previous sample with the following. This code calls the Excel Application object's OnTime method to set up an asynchronous event, giving your automation controller time to release its object references to Excel. When the event occurs, Excel calls the Quit method and exits. Because Quit is called internal to Excel and all Automation references have been released, Excel assumes it is being shut down by the end user and saves the CommandBar changes.

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

				

Press the F5 key to run the project. The message appears and you can confirm the changes to the CommandBars. Click OK and Excel quits when the macro is called. Start Excel and note that the changes to the CommandBars are now saved; the "AddedBar" CommandBar is displayed and the "ExistingBar" CommanBar has been deleted.