Excel VBA - Excel 4 Macro problem

[PaulC]

I want to put cell values into the ge header and then to format heade
text. I have manage to do this using:

With ActiveSheet.PageSetup
.LeftHeader = Range("B52").Value etc

However using this method takes at least 30 seconds for VBA to
set up the page before it will print out. I've tried to use an Excel
macro which I understand is much faster - e.g.:

Application.ExecuteExcel4Macro ("PAGE.SETUP(""&CTEXT"")

This works just putting fixed text into the header, but I have not bee
able to insert the cell value into the header text this way.
There must be a simple solution....

I also wish to format the header text to enlarge and bold it.

Any suggestions?

Pau

 

[Jim Cone]

Paul,

First thing to do is put "ActiveSheet.DisplayPageBreaks = False"
before and after you change PageSetUp. Excel then does not
have to decide where to put them and things can really speed up.

Also, eliminate any PageSetUp settings that you are not changing.

Best would be to have a master/template sheet with the settings already
established and use a copy of that each time you need a new sheet.

If the above doesn't do it for you then using an XL4 macro is a
little faster. But you lose it all in the time it takes to write the code.<g>
The following code adds the content of cell D5 to the
center header and makes the font size 12...

'------------------------------------------
Sub TestXL4Macro()
Dim strCenter As String
Dim strSize As String
Dim nn As Long

'font Size
nn = 12
'So XL4 can use it
strSize = "&" & nn
'assign font size to cell value
strCenter = strSize & Range("D5").Value
'put it all together
strCenter = "PAGE.SETUP(" & """&C" & strCenter & """)"
Application.ExecuteExcel4Macro strCenter
End Sub
'-------------------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[PaulC]

Jim

Many thanks "ActiveSheet.DisplayPageBreaks = False" has certainl
solved the delay problem so I don't need to resort to XL4 macros.

I am afraid I still can't seem to reformat the header say to Bold Aria
16pt. I have tried:

"&""Arial,Bold""&16& Range("B52").value

and various combinations of "s and &s to no avail.

Help with this would be much appreciated,

Pau

 

[Jim Cone]

Paul,

This just tested ok for me...

Dim strValue As String
With ActiveSheet.PageSetup
strValue = Range("B52").Value
.CenterHeader = "&""Arial,Bold""&16" & strValue
End With

Regards,
Jim Cone
San Francisco, CA

 

[PaulC]

Many, many thanks Jim

Your last suggestion worked perfectly!

Paul