VBA Code Slows When Setting 'Page Setup'

[JayKay]

Hi

Hope one of the MVP can help me out with this one. Is there any way to stop my VBA code slowing to a snails pace when altering Page Setup? I have a couple of toggle buttons on my sheets that change between centred printing for one-offs and offset printing with a page footer if the report is going into a bound document. The problem becomes even worse if the default printer is a colour printer! I've tried using the 'Application.ScreenUpdating = False', but ths doesn't help.

Thanks in advance.

JayKay

 

[Colo]

Hi JayKay,

How about this?
Please check the following thread.

Colo Posted: 2002-05-14 23:08
http://www.mrexcel.com/board/viewtopic.php?topic=8342&forum=2&start=10

 

[Ivan F Moala]

On 2002-05-24 01:20, JayKay wrote:
Hi

Hope one of the MVP can help me out with this one. Is there any way to stop my VBA code slowing to a snails pace when altering Page Setup? I have a couple of toggle buttons on my sheets that change between centred printing for one-offs and offset printing with a page footer if the report is going into a bound document. The problem becomes even worse if the default printer is a colour printer! I've tried using the 'Application.ScreenUpdating = False', but ths doesn't help.

Thanks in advance.

JayKay

Hi JayKay
Colo provided a solution to this using sendkeys
Another variation to this was to use the old xl4 macro...I testd these and they are both
about the same interms of speed. But setting
it up is easier with Colos.
Both are FASTER by far then the Std method.
Also try setting the viewpagebreaks to off.

<pre/>
Sub Test1()
'Colo
St = Timer
Application.SendKeys "{ENTER}", False
Application.Dialogs(xlDialogPageSetup).Show _
Arg1:="&""Arial,Bold""&18SampleTable�@", _
Arg3:=0.590551181102362, _
Arg4:=0.590551181102362, _
Arg5:=0.866141732283465, _
Arg6:=0.275590551181102, _
Arg9:=True, _
arg10:=True, _
Arg11:=2, _
Arg12:=8, _
Arg18:=0.590551181102362, _
Arg19:=0.511811023622047
[B2] = Timer - St
End Sub


Sub Test3()
'Mine

Dim sHead As String, sFoot As String, left, right, top, bot, hdng, grid, h_cntr, v_cntr
Dim orient, paper_size, iscale, pg_num, pg_order, bw_cells, quality
Dim head_margin, foot_margin, notes, draft

'// NB: If any of the Variables are set wrong you will
'// Get NO error BUT it won't setup as you want.
ExecuteExcel4Macro ("Page.Setup(""Hello"",""Testing"",0.75,0.75,1,1,FALSE,FALSE,FALSE,FALSE,2,9,TRUE,100,1,FALSE,360,0.5,0.5,FALSE,FALSE)")

End Sub

</pre>

 

[Ivan F Moala]

Hey Colo, you were too fast for me!

Actually when you test the speeds more
the xl4 is faster

 

[JayKay]

Hi Guys

Many thanks for the responses, I'll give them a try and test for speed.

I can't belive that I've been using Excel day in, day out for the last 8 years (VBA for the last 4) and yet I've only just dicovered this message board, I think it will be a god send!

Many thanks

JayKay

 

[JayKay]

Hi Ivan / Colo

The Sendkeys method has worked a treat and my code is now running much faster.

I've managed to work out what most of the argument numbers refer to in the code via a little trial and error, but was wondering if there are any resources, either on the net, or within Excel that would list the argument numbers and what item they refer to on this and other Standard Dialog boxes. The tooltips in VBA just list the argument numbers in order and they don't seem to bare any relation to the order the fields appear on the PageSetup dialog.

Cheers again

JayKay

 

[JayKay]

Bump

 

[Ivan F Moala]

On 2002-05-24 02:49, JayKay wrote:
Hi Ivan / Colo

The Sendkeys method has worked a treat and my code is now running much faster.

I've managed to work out what most of the argument numbers refer to in the code via a little trial and error, but was wondering if there are any resources, either on the net, or within Excel that would list the argument numbers and what item they refer to on this and other Standard Dialog boxes. The tooltips in VBA just list the argument numbers in order and they don't seem to bare any relation to the order the fields appear on the PageSetup dialog.
Cheers again

JayKay

HI JK
from the help files on xlDialogPageSetup
I think they are in numerical order and
are the same that I listed for the ExecuteExcel4Macro.

head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft

I was going to tidy it up a bit....