Text in Footer overlaps data in spreadsheet

[marie]

I have a vba macro that inserts about 4 lines of text in the excel footer. To make it simple, if I have a one page worksheet and the data contained in the excel worksheet is almost to the page break, once I insert the footer via the macro and print preview the document, the footer overlaps some of the data at the bottom of the page.

In ms word, a page break will automatically be created but not in excel. How can I check if the footer is going to overlap the data in vba?

Any help would be appreciated.

[RonMcK]

How about increasing your worksheet's bottom margin so you give the footer more space?

Cheers,

[lucas]

Marie, what does it look like in print preview?

[marie]

I don't want the user to modify the margins manually or enter breaks manually I would like to do it thru code.

Thanks!

[lucas]

Marie, can you post the code you are using to create the footer?

Another question, can you spread your lines out to the left and right footers instead of putting it all in the middle?

[RonMcK]

Lucas,

So far, Marie has no code. I believe that she wants us to help her by crafting some code to automatically adjust the height of the footer, based on the text in it.

Below is a macro that I created to 'scope out' the challenge. I used her file and recorded changing her footer text to 12 pt and, for grins, changed the bottom margin to be 1.75". This looks like it will work, however, it is pegged to her 6 lines of text.

the better solution would calculate the vertical space required dynamically, accumulating the pt size of each line, adding something (1/4"?) for buffer between bottom of the body text and first footer line and the last footer line and bottom of the page.

Is there a Worksheet_BeforePrint event or would it work best to use a BeforeSave event to trigger the 'resize' of the bottom margin?

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/6/2008 by Ron McKenzie
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = _
"&12Line 1 Text abcdefghijklmnop" & vbLf & "Line 2 Text abcdefghijklmnop" & vbLf & _
"Line 3 Text abcdefghijklmnop" & vbLf & "Line 4 Text abcdefghijklmnop" & vbLf & _
"Line 5 Text abcdefghijklmnop" & vbLf & "Line 6 Text abcdefghijklmnop"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
End Sub
P.S. I remember that I still owe you a photograph or two from the old neighborhood. ;-)

Cheers,

[lucas]

Seems to deal with her sample just fine Ron. There is no worksheet before print event that I know of although you could use the workbook beforeprint event and have it just operate on select sheet or sheets.

I would love to see the pictures Ron. Recently one of the kids who grew up with mine graduated from college and took a job with Raytheon in Orlando. They put him up in a condo on Winter Garden Road. I may come down and visit him in the near future and see the old area's for myself.

[RonMcK]

Marie,

As Lucas asked in #5, above, please post the code you use to enter your footer.

Thanks,

[marie]

Ron and Lucas,

Thanks for all your help. My code is on a separate network so I would have to manually type it in and that's why I did not include it. I'm updating existing code that writes to the footer and the problem is we can't change the margins or the fonts to solve the problem. I'm aware that there might not be a solution.

I was just hoping there was someone excel vba method that could repaginate the spreadsheet after I insert the footer. Thus taking the text in the footer in consideration when calculating page breaks.

Thanks again and I appreciate your time.

[RonMcK]

Marie,

Unlike MS Word, MS Excel is unable to automatically adjust its pagination as Headers/Footers are added, enlarged, reduced, or removed. The user is left with the job of balancing the upper and lower margins as lines are added or deleted and font sizes are changed.

If your code enters a fixed number of lines of text which will always be formated in a particular font and size, then, you can manually determine the appropriate margins. You can then write code setting the margins; this should solve your problem. Else, if the number of lines of text and/or font size change based on options selected in running the repoort, you'll need to either set the margins to the largest possible size or use code to compute the margins for you at run time.

I'm still interested in writing some code to decipher the header and footer settings to determine the 'correct' margins. I'm just uncertain how soon I'll have a working piece for you and others to try.

Thanks,