TopMargin value

[Robb]

Can anyone tell me how I can get the value necessary to plug into the
ActiveSheet.PageSetup.TopMargin property so the page won't print mixed
in with the Header?

Robb
Excel 2000
<remove autospam deterrent from address before emailing>

[Dick Kusleika]

Robb

ActiveSheet.PageSetup.TopMargin = ActiveSheet.PageSetup.HeaderMargin

HTH
--
Dick Kusleika
MVP - Excel

"Robb" <robbD...@DELETEtarus.com> wrote in message
news:3c8cf2e4.162262578@gateway...

[Robb]

Dick,

Thanks for your response but that doesn't seem to work.

The TopMargin is the beginning of the page and the HeaderMargin is the
beginning of the Header. Setting the TopMargin to equal the
HeaderMargin will cause the page to print completely over the Header.

TopMargin = HeaderMargin + HeaderSize is what I would need but there
is no such animal.

On Mon, 11 Mar 2002 12:52:08 -0600, "Dick Kusleika"
<di...@paragonconstructioninc.com> wrote:

>ActiveSheet.PageSetup.TopMargin = ActiveSheet.PageSetup.HeaderMargin

[Dick Kusleika]

Robb

That's just a poorly thought out response on my part. Here's a macro that
will work as long as you have no formatting (e.g. bold, font size) in your
header. I'm working on one that will take formatting into account which I
will post.

This one counts the number of lines in the headers and increases the top
margin based on that.

Sub FindMargin()

Dim LHLines As Integer
Dim CHLines As Integer
Dim RHLines As Integer
Dim MaxLines As Integer
Dim HSize As Double
Dim PS As PageSetup

Set PS = Sheet1.PageSetup

LHLines = Len(PS.LeftHeader) - _
Len(Application.WorksheetFunction. _
Substitute(PS.LeftHeader, Chr(10), "")) + 1
RHLines = Len(PS.RightHeader) - _
Len(Application.WorksheetFunction. _
Substitute(PS.RightHeader, Chr(10), "")) + 1
CHLines = Len(PS.CenterHeader) - _
Len(Application.WorksheetFunction. _
Substitute(PS.CenterHeader, Chr(10), "")) + 1

MaxLines = Application.WorksheetFunction.Max(LHLines, RHLines, CHLines)

HSize = MaxLines * 13.5

PS.TopMargin = PS.HeaderMargin + HSize

End Sub

HTH
--
Dick Kusleika
MVP - Excel

"Robb" <robbD...@DELETEtarus.com> wrote in message

news:3c8d034a.166460296@gateway...

[Dick Kusleika]

Robb

Here's a macro and two functions that take into account the font size of the
headers. Bolding, underlining, etc. probably don't make a great difference,
so I didn't account for them.

Sub FindMargin()

Dim LHLines As Double
Dim CHLines As Double
Dim RHLines As Double
Dim MaxLines As Double
Dim ps As PageSetup

Set ps = Activesheet.PageSetup

LHLines = TMSize(ps.LeftHeader)
RHLines = TMSize(ps.RightHeader)
CHLines = TMSize(ps.CenterHeader)

MaxLines = Application.WorksheetFunction.Max(LHLines, RHLines, CHLines)

ps.TopMargin = ps.HeaderMargin + MaxLines

End Sub

Function GetFontSize(Hd As String) As Double

Dim i As Integer
Dim FntSz As String

If Hd Like "*&#*" Then
For i = 1 To Len(Hd)
If Mid(Hd, i, 1) = "&" And Mid(Hd, i + 1, 1) Like "#" Then
FntSz = Mid(Hd, i + 1, 1)
If Mid(Hd, i + 2, 1) Like "#" Then
FntSz = FntSz & Mid(Hd, i + 2, 1)
End If
End If
Next i
Else
FntSz = Application.StandardFontSize
End If

GetFontSize = ((FntSz - 10) * 1.1) + 13.5

End Function

Function TMSize(Hd As String) As Double

TMSize = (Len(Hd) - Len(Application.WorksheetFunction. _
Substitute(Hd, Chr(10), "")) + 1) * _
GetFontSize(Hd)

End Function

HTH
--
Dick Kusleika
MVP - Excel

"Dick Kusleika" <di...@paragonconstructioninc.com> wrote in message
news:OnEh3oTyBHA.428@tkmsftngp03...

[Robb]

Dick,

I'd like to thank you very much for all the time you obviously
put into answering my question. I really appreciate it. (and I'll work
on the implementation right after this post).

I'd like rant for a second if I may (sorry, every so often I just have
to let off steam regarding microsoft)

<ahem>
You're kidding. I mean, I know it's a Microsoft product but come on.
Noone prints reports with Excel? (or I should say, Noone prints
reports with headers in Excel?) It 'looks' as if 2 days before the
release date they told the programmers to hurry up and add
headers/footers to the application.

Actually, I *am *very surprised that given the fact that there are no
properties or methods to allow you to print a report immediately after
a header, that I haven't seen tons of requests for 'How do I do
this?'.
Is printing a report with a header really that bizarre of a request?
<end ahem>

Thanks again for your help
(perhaps some day after I cure my newbie-itis I'll be able to return
the favor)

Robb

[Dick Kusleika]

Robb

Your welcome, I works for you.

Regarding your rant: I don't know what your application is, but I print
headers all the time. I don't, however, change my headers all the time. So
I can set my TopMargin and HeaderMargin manually and virtually never have to
change them. Do you change the number of lines or the font size of your
headers often?

--
Dick Kusleika
MVP - Excel

"Robb" <robbD...@DELETEtarus.com> wrote in message

news:3c8e0d82.234612546@gateway...

[Dick Kusleika]

make that

I hope it works for you.

"Dick Kusleika" <di...@paragonconstructioninc.com> wrote in message

news:ehWHw2dyBHA.2784@tkmsftngp07...

[Robb]

I'm programming a report generator based on csv data that our software
kicks out. I'm going to give the users as much flexibility as I can
within my module but per management I have to shield them from a
working knowledge of Excel as much as possible.

(The thinking being that if the customer wants to and has the
abilities, they can customize their reports above and beyond what I
give them but if they don't want to, don't have the time to, can't,
learn Excel, then they will still be able to generate decent
reports.)

Of course the header information will vary from customer to customer
as well as report to report. If you've ever had to program for users
you've never met, you know that at some point or other, someone will
try something you never expected. As a matter of fact, it's entirely
possible that there could be multiple font sizes on multiple lines in
the header. So I studied your code and made a function of my own that
searched for all possible fontsize settings on all possible lines and
added up the largest from each line to get the total for the section
and then applied your formula to it (well, I'm in the process anyway)

So (FntSz - 10) * 1.1) + 13.5 is the formula to convert a fontsize
into margin values? Where did you find that one? :-)
Given: 18pt font
18 - 10 = 8
8 * 1.1 = 8.8
8.8 + 13.5 = 22.3
so &18 in the header means 18point font which comes out the other end
of your formula as 22.3 which is applied to a margin, correct?

On Tue, 12 Mar 2002 10:05:41 -0600, "Dick Kusleika"

[Dick Kusleika]

Robb

That's a good reason. Since I develop almost exclusively for my office, my
skills at identifying potential problems are poor. They just come and tell
me when something goes wrong. All that is evidenced by the fact that I
didn't account for multiple fonts in the header.

Here's the technical details:
I put a one line header in with a font size of 10. Then I manually set the
TopMargin where I would want it. It came out to about 13.6. Then I did the
same thing with a 20 point header and it came out to about 24.5.

So I used 10 and 13.5 as the base and increased it 11 for every 10 point
size increase (1.1)

Pretty technical, huh?

You could just, and probably should, use the actual point size in a formula
instead of 10 and 13.5 and just add a little gutter margin to the header
area so the header area isn't exactly the size of the header.

I'd like to see the final function/macro when you get it done.

--
Dick Kusleika
MVP - Excel

"Robb" <robbD...@DELETEtarus.com> wrote in message

news:3c8e3cd2.246724500@gateway...

[Robb]

Thanks again for all your help Dick. I'm emailing you the final
version of my function.

On Tue, 12 Mar 2002 14:33:43 -0600, "Dick Kusleika"
<di...@paragonconstructioninc.com> wrote:

>Robb
>

>That's a good reason. Since I develop almost exclusively for my office, my
>skills at identifying potential problems are poor. They just come and tell
>me when something goes wrong. All that is evidenced by the fact that I
>didn't account for multiple fonts in the header.
>
>Here's the technical details:
>I put a one line header in with a font size of 10. Then I manually set the
>TopMargin where I would want it. It came out to about 13.6. Then I did the
>same thing with a 20 point header and it came out to about 24.5.
>
>So I used 10 and 13.5 as the base and increased it 11 for every 10 point
>size increase (1.1)
>
>Pretty technical, huh?
>
>You could just, and probably should, use the actual point size in a formula
>instead of 10 and 13.5 and just add a little gutter margin to the header
>area so the header area isn't exactly the size of the header.
>
>I'd like to see the final function/macro when you get it done.

Robb