Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    We are currently testing SP2 for Office 2007 and have one issue out standing. We have a macro that a user can run to place information into the footer of all the worksheets he has in the current workbook. This macro has been running since 2001 using Office 2000.

    The error we're getting is:

    Run-time Error 1004
    Unable to set the LeftFooter property of the PageSetup class



    [codebox]Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim sh As Worksheet
    str_Slash = Chr(92) ' \
    str_End = Chr(13) & "&A" & Chr(13) & "&D" & Chr(32) & "&T" ' Sheet name, date and time
    str_Unit = "CS"
    For Each sh In ActiveWorkbook.Worksheets
    sh.PageSetup.LeftFooter = "&""Arial,Regular""&6" & _
    VBA.UCase(str_Unit & Chr(58) & Application.ActiveWorkbook.Path & _
    str_Slash & Application.ActiveWorkbook.Name) & str_End

    Next
    Set sh = Nothing
    End Sub
    [/codebox]

    We are getting the error on the PageSetUp.LeftFooter line of the following code.


    Does anyone have any idea what could be causing the problem in setting the LeftFooter

    Thank you
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could try moving the code to a procedure in a standard module and to call this procecure from the BeforeClose event module.

    In a standard module:

    Code:
    Public Sub MyClose()
      Dim sh As Worksheet
      ...
      ...
      Set sh = Nothing
    End Sub
    and in the ThisWorkbook module:

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
      Call MyClose
    End Sub
    Note: make sure that a default printer has been set up on the PC on which you test Office 2007 SP2. PageSetup will fail if there is no printer.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans

    Thank you. The problem was that the default printer was not installed.

    As a side note the code I showed is generated by an XLAM macro when the user runs it. It also generates the same code for the Open Event incase the user moved the file.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='DJLansing' post='777164' date='27-May-2009 17:30']As a side note the code I showed is generated by an XLAM macro when the user runs it. It also generates the same code for the Open Event incase the user moved the file.[/quote]
    Why not put the code in the Workbook_BeforePrint event procedure? That way, the footer will always be correct when the workbook is printed, even if the user has used Save As to save the workbook under another name and/or in another location.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you don't have to have the workbook name in uppercase, you could also just use &Z&F to get the full name.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •