Microsoft KB Archive/893286

= Your changes to the values of the header and of the footer may not be applied in Microsoft Office Excel =

Article ID: 893286

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Office Excel 2007

-





SYMPTOMS
When you try to set the values of the header and of the footer in Microsoft Office Excel 2003 or in Microsoft Office Excel 2007 by using code in the WorkbookBeforeSave event, your changes may not be applied.



CAUSE
This behavior occurs when a macro in the workbook implements the WorkbookBeforeSave event handler that tries to set the values of the header and of the footer. When the Save method of the workbook is called from a COM add-in, the code in the WorkbookBeforeSave event is executed. However, the changes to the header and to the footer are not applied.



WORKAROUND
To work around this behavior, use the following code. oXL.CommandBars(&quot;Standard&quot;).Controls(&quot;Save&quot;).Execute Use this code instead of calling the following code from the COM add-in. oXLWb.Save



Steps to reproduce the behavior
 Create a COM add-in. To do this, follow these steps:  Start Microsoft Visual Basic 6.0, and then select Addin as the project type. The Connect designer class and the frmAddin form are added to the project. Open the Designer window for the Connect class. In the Application list, click Microsoft Excel. In the Initial Load Behavior list, click Startup. Remove the frmAddin form from the project.</li> In the Project window, right-click Connect, and then click View code.</li> Remove all the code in the Designer window.

Note This code works for Visual Basic add-ins, but this code does not work for Office add-ins.</li>  Add the following code to the Designer window. Option Explicit

Dim oXL As Object Dim oXLWb as Object Dim WithEvents MyButton As Office.CommandBarButton Private Sub AddinInstance_OnConnection(ByVal Application As Object, _   ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _    ByVal AddInInst As Object, custom As Variant) On Error Resume Next 'MsgBox &quot;My Addin started in &quot; & Application.Name Set oXL = Application Set oXLWb = oXL.ActiveWorkbook Set MyButton = oXL.CommandBars(&quot;Standard&quot;).Controls.Add(1) With MyButton .Caption = &quot;My Custom Button&quot; .Style = msoButtonCaption ' The following items are optional, but recommended. ' The Tag property lets you quickly find the control ' and helps MSO keep track of it when there is more than ' one application window visible. The property is required ' by some Office applications and should be provided. .Tag = &quot;My Custom Button&quot; ' The OnAction property is optional, but recommended. ' It should be set to the ProgID of the add-in, in such a way that if         ' the add-in is not loaded when a user clicks the button, ' MSO loads the add-in automatically and then raises ' the Click event for the add-in to handle. .OnAction = &quot;!<&quot; & AddInInst.ProgId & &quot;>&quot; .Visible = True End With End Sub Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _     AddInDesignerObjects.ext_DisconnectMode, custom As Variant) On Error Resume Next 'MsgBox &quot;My Addin was disconnected by &quot; & _ '  IIf(RemoveMode = ext_dm_HostShutdown, _      '   &quot;Excel shutdown.&quot;, &quot;end user.&quot;) MyButton.Delete Set MyButton = Nothing Set oXL = Nothing End Sub Private Sub MyButton_Click(ByVal Ctrl As Office.CommandBarButton, _    CancelDefault As Boolean) oXLWb.Save End Sub </li> On the File menu, click Make MyAddin.dll. Visual Basic registers the add-in for you.</li></ol> </li> In Excel, create a new blank workbook, and add a macro to the workbook. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> Start Excel, and then open a new workbook.</li> Press ALT+F11 to start the Visual Basic Editor.</li> On the Insert menu, click Class Module.</li>  In the Class1 module window, type the following code. Private WithEvents objXLApp As Application

Private Sub Class_Initialize Set objXLApp = Application End Sub

Private Sub Class_Terminate Set objXLApp = Nothing End Sub Private Sub objXLApp_WorkbookBeforeSave(ByVal wbkSaveBook _               As Excel.Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim shtSheet As Object Dim strHeaderFooter As String

wbkSaveBook.ActiveSheet.Range(&quot;a1&quot;).Value = &quot;Test&quot; ActiveSheet.PageSetup.LeftFooter = &quot; LH&quot;

'  Put the footer into all sheets For Each shtSheet In wbkSaveBook.Sheets

With shtSheet.PageSetup

.LeftHeader = &quot; LH&quot; .CenterHeader = &quot; CH&quot; .RightHeader = &quot; RH&quot;

End With

Next shtSheet wbkSaveBook.Save

End Sub </li></ol> </li> On the View menu, click Properties Window.</li> In the Properties window, type clsEvents in the (Name) box to rename the Class1 module.</li> On the Insert menu, click Module.</li>  In the Module1 window, type the following code. Private EventClass As clsEvents

Sub sAutoOpen

Set EventClass = New clsEvents

End Sub .

Note When you use Excel 2007, save the workbook as a Macro-enabled workbook before you go to step 7. </li> Position the insertion point in the text of the sAutoOpen subroutine. On the Run menu, click Run Sub/UserForm.</li> On the File menu, click Close and Return to Microsoft Excel.</li> <li>Click My Custom Button.

Note The COM add-in that you created in step 1 created this button. When you use Excel 2007, the My Custom Button button is located under Add-Ins on the Ribbon.</li> <li>In Excel 2003, click Page Setup on the File menu.

In Excel 2007, click the Page Layout menu. In the Page Setup group, click Page Setup to open the Page Setup dialog box.</li> <li>Click the Header/Footer tab. Notice that the header information and the footer information that was set by the WorkbookBeforeSave event was not applied.</li></ol>

Additional query words: XL2007

Keywords: kbbug kbtshoot kbaddin KB893286

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.