Your changes to the values of the header and of the footer may not be applied in Microsoft Office Excel 2003 (893286)

MORE INFORMATION

Steps to reproduce the behavior

1. Create a COM add-in. To do this, follow these steps:
   1. 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.
   2. Open the Designer window for the Connect class.
   3. In the Application list, click Microsoft Excel.
   4. In the Initial Load Behavior list, click Startup.
   5. Remove the frmAddin form from the project.
   6. In the Project window, right-click Connect, and then click View code.
   7. 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.
   8. 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 "My Addin started in " & Application.Name
         
               Set oXL = Application
               Set oXLWb = oXL.ActiveWorkbook
       
            Set MyButton = oXL.CommandBars("Standard").Controls.Add(1)
               With MyButton
                  .Caption = "My Custom Button"
                  .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 = "My Custom Button"
       
                ' 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 = "!<" & AddInInst.ProgId & ">"
       
                  .Visible = True
               End With
         
         End Sub
       
         Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As _
            AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
            On Error Resume Next
            'MsgBox "My Addin was disconnected by " & _
            '   IIf(RemoveMode = ext_dm_HostShutdown, _
            '   "Excel shutdown.", "end user.")
            
            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
      

   9. On the File menu, click Make MyAddin.dll. Visual Basic registers the add-in for you.
2. In Excel 2003, create a new blank workbook, and add a macro to the workbook. To do this, follow these steps:
   1. Start Excel 2003, and open a new workbook.
   2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
   3. On the Insert menu, click Class Module.
   4. 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("a1").Value = "Test"
          ActiveSheet.PageSetup.LeftFooter = " LH"
      
          '   Put the footer into all sheets
          For Each shtSheet In wbkSaveBook.Sheets
      
              With shtSheet.PageSetup
      
                  .LeftHeader = " LH"
                  .CenterHeader = " CH"
                  .RightHeader = " RH"
      
              End With
      
          Next shtSheet
         wbkSaveBook.Save
      
      End Sub

3. On the View menu, click Properties Window.
4. In the Properties window, type clsEvents in the (Name) box to rename the Class1 module.
5. On the Insert menu, click Module.
6. In the Module1 window, type the following code.

   Private EventClass As clsEvents
   
   Sub sAutoOpen()
   
       Set EventClass = New clsEvents
   
   End Sub

7. Position the insertion point in the text of the sAutoOpen subroutine. On the Run menu, click Run Sub/UserForm.
8. On the File menu, click Close and Return to Microsoft Excel.
9. Click My Custom Button.
   
   Note The COM add-in that you created in step 1 created this button.
10. On the File menu, click Page Setup.
11. Click the Header/Footer tab. Notice that the header information and the footer information that was set by the WorkbookBeforeSave event was not applied.