Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
header formatting
I create a header in a work book using
With ActiveSheet.PageSetup .RightHeader = "&B" & ActiveWorkbook.Name & "&B" & Chr(13) & "&A" & Chr(13) & "&D" & Chr(13) & "Page &P of &N" .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 ActiveWindow.Zoom = 75 End With This macro is in file xxx. When the user runs this macro and saves the file to yyy.xls. I want the first line of the header to be yyy.xls. Currently if the user runs the macro and saves the file to a different name. The header is xxx.xls. What am I missing? Thanks for any help Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
header formatting
Well I have been reading and this seems to be an issue. On
http://www.j-walk.com/ss/excel/tips/tip22.htm It says that the filename will not be changed. I am try to use a work around to set the headers before printing and before save or save as. The before print works well and the before save is working but is there a before saveas? Code below Tahnks Mike Here is what I have: Function FixHeaders() Dim wkbk As Workbook Dim wksht As Worksheet Set wkbk = ActiveWorkbook Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets sht.Activate ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .RightHeader = "&B" & ActiveWorkbook.Name & "&B" & Chr(13) & "&A" & Chr(13) & "&D" & Chr(13) & "Page &P of &N" .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 ActiveWindow.Zoom = 75 End With Next sht End Function '&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call FixHeaders End Sub Private Sub Workbook_BeforePrint(Cancel As Boolean) Call FixHeaders End Sub "quartz" wrote: Mike, Try replacing "ActiveWorkbook.Name" with "&F" that may work... HTH "Mike" wrote: I create a header in a work book using With ActiveSheet.PageSetup .RightHeader = "&B" & ActiveWorkbook.Name & "&B" & Chr(13) & "&A" & Chr(13) & "&D" & Chr(13) & "Page &P of &N" .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 ActiveWindow.Zoom = 75 End With This macro is in file xxx. When the user runs this macro and saves the file to yyy.xls. I want the first line of the header to be yyy.xls. Currently if the user runs the macro and saves the file to a different name. The header is xxx.xls. What am I missing? Thanks for any help Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
header formatting | Excel Programming | |||
Header formatting problem | Excel Programming | |||
Formatting separate lines of header | Excel Programming | |||
Formatting Codes for header and footer | Excel Programming | |||
Formatting a header with VBA | Excel Programming |