Using xlDialogPageSetup for Fast Page Setup to set PrintArea / TitleRows ?

[deadstone]

Hi,

I have had some success doing a fast PageSetup in MS Excel using the xlDialogPageSetup and the XL4 Macro PAGE.SETUP.

I have even got SET.PRINT.AREA and SET.PRINT.TITLES to work, which is again better than using Activesheet.pagesetup.

But is there a way to use the xlDialogPageSetup (Application.Dialogs(xlDialogPageSetup).show...) method to set the PrintArea and the PrintTtitle rows?

BTW - in case anyone needs it, the original MS Help file for all of these Excel 4 Macro functions is on the MS Website:
http://support.microsoft.com/default.aspx?scid=kb;en-us;128185

thanks
Philip

 

[Andrew Poulsom]

To set the print titles:

Application.Dialogs(xlDialogSetPrintTitles).Show Rows(1), Columns("A:B")

 

[deadstone]

To set the print titles:

Application.Dialogs(xlDialogSetPrintTitles).Show Rows(1), Columns("A:B")

Thanks, I never even thought there might be a special option!

 

[Kenneth Hobson]

A good list of dialogs and arguments is found at http://www.code-vb.com/fragments/Excel_Dialogs.htm

 

[deadstone]

A good list of dialogs and arguments is found at http://www.code-vb.com/fragments/Excel_Dialogs.htm

Thanks, I had a look but it seems there is no way from VBA using the Dialog Show method to set the Print Area, so I'll stick with the Excel 4 Macro function SET.PRINT.AREA

Philip

 

[Kenneth Hobson]

I am not sure why PrintArea is not as good as the excel4 method. Maybe you can post your excel4 code?

Sub t()
  ActiveSheet.PageSetup.PrintArea = "A1:B5"
  Application.Dialogs(xlDialogPrint).Show
End Sub

We discussed the xlDialogPageSetup and the excel4 method in this thread. If you have trouble using an array for a parameter in these commands, the thread is worth the read.http://www.mrexcel.com/forum/showthread.php?t=337551

 

[deadstone]

I am not sure why PrintArea is not as good as the excel4 method. Maybe you can post your excel4 code?

Sub t()
  ActiveSheet.PageSetup.PrintArea = "A1:B5"
  Application.Dialogs(xlDialogPrint).Show
End Sub

We discussed the xlDialogPageSetup and the excel4 method in this thread. If you have trouble using an array for a parameter in these commands, the thread is worth the read.http://www.mrexcel.com/forum/showthread.php?t=337551

It's really because the XL4 method and the XLDialogs method are so much faster than using ActiveSheet.PageSetup when setting several features dynamically...

On the MS Excel.Programming board I learnt from another MVP (Jon Peltier) that we can use PrintArea and PrintTitles as defined names...which is why they're not included in the XLDialog or XL4 PAGE.SETUP.

The link to Jon's reply is here:
http://www.microsoft.com/communitie...57d180-fc1a-44be-a116-df9e76ca1264&sloc=en-us

BTW - in case anyone needs it, the original MS Help file for all of these Excel 4 Macro functions is on the MS Website:
http://support.microsoft.com/default.aspx?scid=kb;en-us;128185

regards
Philip

 

[zakasnak]

I visited all of the sites listed in the various stages of this post & downloaded the XLM function help file.

My question is which syntax would I use? I'm wanting to add this to an existing macro (one that currently takes FOREVER). I think syntax 1 for worksheets & macro sheets, but I'm adding to visual basic, so maybe syntax 3 for vb modules?

Here's my existing code, if anyone wants to help me change this to XLM4pageSetUp

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

 

[zakasnak]

I've come up with this using the help files:

With UseXLM4pageSetUp()
Application. ExecuteExcel4Macro ("Page.Setup("","",0.75,0.75, 0.75,0.75,FALSE,FALSE,TRUE,FALSE,2,1,100,PAGE_NUMBER,PAGE_ORDER,FALSE,600,0.5,0.5,FALSE,FALSE)")
End With

Can I assume PAGE_NUMBER = 1? I know PAGE_ORDER refers to the "down, then over" statement, would that then be TRUE?

Will this code even work inside the current macro running in place of what I posted above?

HELP?!