Print portion of file path and tab name as footer automatically on ss

[JSH720]

I need to do two things, on ss open, ask if you want to print the filename ( see below) if yes, then
2. Print the file name and the last two folders going left plus the tab name oas footer in spreadsheet. for esxample if the path is x: jsh\nw\ HOUSTON\AREAS.XLS,then the filename that would print aws a footer (if you selected yes on the box on opening the spreadsheet): nw\ HOUSTON\AREAS.XLS. along with the tab nme : tab: tab name . It needs to go on the left footer and be Calib ri 9 pt font. In the right will be the date and time last updated and saved.

Thanks!

 

[John_w]

Try this code, which sets the left footer if the reply is Yes and clears it if the reply is No.

ThisWorkbook module:

Private Sub Workbook_Open()
    Set_Left_Footer MsgBox("Do you want to print the partial file path and name in the left footer?", vbYesNo)    
End Sub

Standard module:

Public Sub Set_Left_Footer(reply As Long)
    
    Dim p As Long
    Dim LeftFooterText As String
    
    If reply = vbYes Then
        p = InStrRev(ThisWorkbook.FullName, "\")
        p = InStrRev(ThisWorkbook.FullName, "\", p - 1)
        p = InStrRev(ThisWorkbook.FullName, "\", p - 1)
        LeftFooterText = Mid(ThisWorkbook.FullName, p + 1) & " tab: " & ActiveSheet.Name
        ActiveSheet.PageSetup.LeftFooter = "&""Calibri""&9 " & LeftFooterText
    Else
        ActiveSheet.PageSetup.LeftFooter = ""
    End If
    
End Sub