Microsoft KB Archive/241652

= FIX: Changes Made to Excel CommandBars Through Automation Are Not Saved =

Article ID: 241652

Article Last Modified on 12/15/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q241652



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
To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

To Temporarily Work Around This Problem

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 that this is a bug in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.



Steps to Reproduce Behavior
 Start Excel. Click Toolbars on the View Menu and then choose Customize. Click on the New button in the Customize dialog box to add a new CommandBar. Type ExistingBar for the Toolbar name and click OK. Close the Customize dialog box and quit Excel.</li> Open a new Visual Basic EXE project. Form1 is created by default.</li> 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).</li>  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 </li> Run the project and click the button.</li> When the message is displayed, you can see that the "ExistingBar" CommandBar is deleted and our new CommandBar "AddedBar" is displayed.</li> Click OK and let the program Quit Excel and terminate.</li> Now start Excel, and note that the added CommandBar is no longer there and the deleted bar still exists.</li></ol>

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.

<div class="references_section">