locked
Custom properties in a footer in EXCEL2016 RRS feed

  • Question

  • Hi,

    I have a file with 10 sheets and for each of them, I have to put a custom footer with 2 custom fields (Update_date and version)

    I did not find a way to do this although WORD does it perfectly!

    DO I have to pass through cells in a sheet and than call the values in the footer?

    I really don't know how to manage this. I tried with VBA but none of the macro's on the web are giving me the stored value...

    Thx for your help! :-)

    Thursday, July 23, 2020 10:11 AM

All replies

  • Hi,

    The code for this is:

    Sub MakeFooters()

    Dim TxtDate As String
    Dim TxtVersion As String
    Dim Sht As Worksheet

    TxtDate = "23.07.2020"
    TxtVersion = "1.2:"

    For Each Sht In ThisWorkbook.Sheets
        Application.PrintCommunication = False
       
        With Sht.PageSetup
            .LeftFooter = "Update_date: " & TxtDate & "" & Chr(10) & "Version: " & TxtVersion & ""
        End With
            
        Application.PrintCommunication = True
    Next



    End Sub


    Guy Zommer

    Thursday, July 23, 2020 11:38 AM
  • Another option without macro is to select all Sheets with Ctrl or Shift and then page-setup will apply to all selected sheets

    Guy Zommer

    Thursday, July 23, 2020 11:47 AM
  • Thx,

    Now ho do I define date and version as this is variable and i do not want to edit the macro every time.

    Is there a way to get the value from the file custom properties?

    Robert

    Thursday, July 23, 2020 1:26 PM
  • You are free to use a macro recorder available in Excel and make the required customizations manually, so the code will be generated for you automatically by the recorder. See Record a Macro in Excel – Instructions and Video Lesson for more information.  

    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Thursday, July 23, 2020 6:51 PM
  • Excel Workbook has a property "CustomDocumentProperties". Here you can add properties and access later.

    Best Regards, Asadulla Javed

    Saturday, July 25, 2020 4:42 AM
    Answerer
  • Yes you can do this

               

    Sub MakeFooters()

    Dim TxtDate As String
    Dim TxtVersion As String
    Dim Sht As Worksheet


    TxtDate = ThisWorkbook.BuiltinDocumentProperties.Item("KeyWords")
    TxtVersion = ThisWorkbook.BuiltinDocumentProperties.Item("Comments")

    For Each Sht In ThisWorkbook.Sheets
        Application.PrintCommunication = False

        With Sht.PageSetup
            .LeftFooter = "Update_date: " & TxtDate & "" & Chr(10) & "Version: " & TxtVersion & ""
        End With

        Application.PrintCommunication = True
    Next

    End Sub

    You can get to document properties thru File-->Properties--> Advanced Properties


    Guy Zommer

    Monday, July 27, 2020 5:03 AM