Print Rows

  • Thread starter Thread starter Dean
  • Start date Start date
I recorded a macro when I change the center header to "asdf" and used a font
size of 72.

This was the important part of that recorded macro:

With ActiveSheet.PageSetup
.CenterHeader = "&72asdf"
End With

So my code changed to:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim Cell As Range
Dim HeaderStr As String

Dim mySheetNames As Variant
Dim iCtr As Long

mySheetNames = Array("IS-LA", "BS-LA", "BS-LA-CV")

For iCtr = LBound(mySheetNames) To UBound(mySheetNames)
HeaderStr = "&72" '<--start with that font size string.
With Worksheets(mySheetNames(iCtr))
'do your stuff
For Each Cell In .Range("a1:a4")
HeaderStr = HeaderStr & Cell.Value & vbCr
Next Cell
'Remove last vbCr
HeaderStr = Left(HeaderStr, Len(HeaderStr) - 1)
.PageSetup.CenterHeader = HeaderStr
End With
Next iCtr
End Sub

72 point seems a little large to me, though.
HeaderStr = "72Left(HeaderStr, Len(HeaderStr) - 1)"

Regarding the font size (e.g., size of 72), I tried this and about 20 other
variations (all to no avail), but since I did it (recorded a macro that
enlarged the font size) on text that was already within the text box, it's
not the same as doing this to a variable, I fear. Kindly help.

Thx!
Dean
Click to expand...
 
I could have sworn I tried some variant like that, but it did not work.
This does. Thanks!

Regarding the size of 72, I've buried myself in a hole by accepting editing
of my file by a client who modified it to have fonts of 40. I'm not sure
why, but the header font size adjustment could be smaller if it were 2 pages
wide by 1 page high, as I have set it. But for some reason, it keeps
replacing that setting with a computed % of normal size and when it does
that, it's usually, like, 10%, which renders the headings microscopic -
SHOULD IT BE DOING THIS? This is why I need to make the header so big to
compensate. Also, it is set to have as many as 200 columns, though
typically it only has about 40. I run a macro to hide all the unused
columns. I guess I should reset the width AFTER I delete the empty columns.

I think I need to reject that large font size, I think. The problem is that
the exhibit is too short compared to its width, and the client wants it
taller. Increasing the font size seems to help (though I'm not sure why),
but the price I'm paying for this huge font size is too dear.

Are there any obvious ways to adjust for an exhibit that is too short
compared to its width, other than leaving empty rows in between (or
increasing the row heights)? We really don't want it to stretch over more
than 2, maybe, 3 pages wide, if there are only about 40 columns.

Dean
 
I don't have any guess why the page settings are changing after you've changed
them (you did save your changes I'm guessing???).

I've always set the page layout the way I want it--either based on pages wide or
pages tall. And I leave the one I don't care about empty (not 0, not anything).

But I'm not sure that helps you or your client.
I could have sworn I tried some variant like that, but it did not work.
This does. Thanks!

Regarding the size of 72, I've buried myself in a hole by accepting editing
of my file by a client who modified it to have fonts of 40. I'm not sure
why, but the header font size adjustment could be smaller if it were 2 pages
wide by 1 page high, as I have set it. But for some reason, it keeps
replacing that setting with a computed % of normal size and when it does
that, it's usually, like, 10%, which renders the headings microscopic -
SHOULD IT BE DOING THIS? This is why I need to make the header so big to
compensate. Also, it is set to have as many as 200 columns, though
typically it only has about 40. I run a macro to hide all the unused
columns. I guess I should reset the width AFTER I delete the empty columns.

I think I need to reject that large font size, I think. The problem is that
the exhibit is too short compared to its width, and the client wants it
taller. Increasing the font size seems to help (though I'm not sure why),
but the price I'm paying for this huge font size is too dear.

Are there any obvious ways to adjust for an exhibit that is too short
compared to its width, other than leaving empty rows in between (or
increasing the row heights)? We really don't want it to stretch over more
than 2, maybe, 3 pages wide, if there are only about 40 columns.

Dean
Click to expand...
 
Back
Top