Excel add line to footer or foot of page 1

[cjashwell]

Hi,

I'm working with Excel 2000 under Win2k SP4.

I would like to add a horizontal line to the foot of my worksheet pages as a matter of formatting neatness. What is the best way to do this?

Adding a line using the drawing function on the actual worksheet doesn't seem to be the optimum method, as the distance of the line from any footer text (page numbering, date-time etc) is conditional on the overall page breaking of the sheet. In other words, the line can appear further up or lower down the page depending on how the page breaks are worked out. Furthermore, a line added through this method doesn't necessarily extend from the left to the right margin.

In sum, I want my line to sit exactly at the top of page footer, and to extend from the left margin to the right margin.

Any help gratefully received.

Regards,
CJA

 

[VulcanJedi]

one immediate solution is to click:
VIEW, HEADERS/FOOTERS, then sheet tab. Select, the rows / columns to repeat tabs...
you could format the cells with the border function on some auxillary sheet and then have it grafted to every page....

Tranpkp

 

[cjashwell]

Thanks tranpkp, but doesn't this only apply to rows to be repeated at the top of the page, like a header? I can't see how to get this to work for a footer.

cheers,
CJA

 

[SkipVought]

CJA,

1) Set your PrintArea

2) Runt this code

Sub MarkBreaks()
    With [Print_Area]
        .Select
        c = .Columns.Count + 1
        Columns(c).ClearContents
        .FormatConditions.Delete
        .FormatConditions.Add _
            Type:=xlExpression, _
            Formula1:="=" & Cells(1, c).Address(RowAbsolute:=False, ColumnAbsolute:=True) & "=""~"""
        With .FormatConditions(1).Borders(xlBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
    End With
    End With
    For Each hb In HPageBreaks
        With Cells(hb.Location.Row - 1, c)
            .Value = "~"
            .Font.Color = vbWhite
        End With
    Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

BTW,

THis code ASSUMES that there is NOTHING in the column directly to the RIGHT of the PRINT AREA.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[cjashwell]

Thanks for that SkipVought, I'll give it a try when I'm back at my desk tomorrow (I'm in the UK). Will give you feedback on how I get on with it ...

Many thanks,
CJA

 

[cjashwell]

Hi SkipVought,

Tried the code, but it didn't work. It's not making any changes to the worksheet at all.

I don't know the FormatConditions object, so I'll read up on it to see if I can figure out what you were getting at, but any further help would be appreciated.

Thanks,
CJA

 

[SkipVought]

Tell me EXACTLY what you did to prepare your sheet and run this procedure.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[cjashwell]

OK, the sheet contains a table occupying A120 and a chart taken from that table that sits just below it, down to about row 52. The rows are all 12.75 points high, except for the rows where the table is, which are 15 points high. Column A is 34.57 points wide. The other columns are 8.57 points wide.

The header contains a centred title. The footer contains a left-aligned date & time and right-aligned page numbers. The margins are Top: 2.5, Header: 1.3, Left: 1.7, Right: 2.2, Bottom: 2.5, Footer: 1.3. Center on Page Horizontally is ticked. The sheet has Portrait orientation.

I selected A1:G60 and went File | Print Area | Set Print Area.

Then I ran your code, from the VBE. The print area was selected, but nothing was added to the worksheet.

Would very much appreciate your further feedback.

Thanks,
CJA

 

[SkipVought]

Here what should be happening

Sub MarkBreaks()
    With [Print_Area]
[b]'select the print area[/b]
        .Select
[b]'assign to c the column immediately to the right of the print area[/b]
        c = .Columns.Count + 1
[b]'clear the contents of that column[/b]
        Columns(c).ClearContents
[b]'delete any conditional formats in the print area[/b]
        .FormatConditions.Delete
[b]'add a conditional format formula in print area if there a tilda ~ in the new column then insert a BORDER in cell bottom[/b]
        .FormatConditions.Add _
            Type:=xlExpression, _
            Formula1:="=" & Cells(1, c).Address(RowAbsolute:=False, ColumnAbsolute:=True) & "=""~"""
        With .FormatConditions(1).Borders(xlBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End With
[b]'for each horizontal break in the horizontal breaks collection[/b]
    For Each hb In HPageBreaks
[b]'in the row location of the horizontal break in the new column insert a tilda with white font so the conditional format can insert a bottom border[/b]
        With Cells(hb.Location.Row - 1, c)
            .Value = "~"
            .Font.Color = vbWhite
        End With
    Next
End Sub

After this is run, you ought to be able to select any cell in the print area and examine the conditional formatting (Format/Conditional Format). For instance, in A1 the CF formula should be...
[tt]
=$H1="~"
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[cjashwell]

Ok, thanks, that is indeed what is happening - if I select Format | Conditional Formatting in, say, A60 (right at the bottom of my print area) I see that the formula value is =$H60="~"

I don't see how I get from this to having a horizontal line stretching from the right margin to the left margin at the top of the page footer.

Thanks,
Corin

 

[cjashwell]

Ok, I now see that if I place a ~ in a cell outside but adjacent to my print area I get a horizontal line (a cell border) across my print area.

The problem now is that the print area is not the same as the page margins. I guess that's the core of my question: how do I set a print area that falls precisely within my page margins?

CJA

 

[cjashwell]

Skip,

Just to add, the last part of the code doesn't work because there are 0 page breaks - it's a one page worksheet. I can modify that OK, but it's still drawing lines across the print area, not the area between the margins.

cheers,
CJA

 

[SkipVought]

As you know, ther is no way of putting a graphic format or object in header/footer. So the Conditional Format approch is a technique to accomplish the graphic format thing.

I would suggest modifying the procedure to accomodate what you want. Possibilities for doing this are...

1) Horizontal Center on page in Page Setup.

2) Adjust the columns in the Print Area fill the width you want.

3) Some other ingenious solution out there in Never-Never Land???


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[AnotherHiggins]

CJA,

This is far from a fancy answer, but if your goal is simply to make this one report have a line above the footer text to make it look pretty - which is the impression I get after reading your posts - then why not just go into the Custom Footer and in each section add a bunch of underscores ( ___ ), then enter, then the text you want to show up under the line. This will give you a line above the footer text of however many sheets you may have in a report.

Again, a very simple answer, but it should work.

John

 

[cjashwell]

Skip, I'm gonna give you a star 'coz I really appreciate your help. It's one of those things that's really just beyond the amount of effort required to get it to work precisely right!

I have played around with the print area/margins loads to try to get as close a match as possible, it's just never exactly right. I'm going to settle for a horizontal line drawn as close as possible to the footer, and as near as possible to the margins.

I already tried anotherhiggins solution, but the joins aren't quite right, and the text gets thrown slightly out.

Nevermind - thanks to both of you again.

Best wishes,
CJA