Setting paper size as variable for printing custom views
 

I have a macro which prints a custom view as follows in example 1:

ActiveWorkbook.CustomViews("Main Cover Sheet").Show
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

The print settings for this are set using legal paper. I want to give the
user the choice to switch to letter if they want to. I know I can do this in
code by doing the following in example 2:

ActiveWorkbook.CustomViews("Main Cover Sheet").Show
With ActiveSheet.PageSetup
.PaperSize = xlPaperLetter
End With

What I want to do is have the user choose via an option box. I want to then
pass the choice to a variable in VBA - say "papersize". How do you pass this
variable to the code I already have without repeating all the code in example
2. Can you integrate into the first line of code with a comma (i.e.
Collate:=TRUE, PaperSize: xlPaperLetter). Or do you need the second line?

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.PageSetup.PaperSize = xlPaperLetter

Thanks

EM

Setting paper size as variable for printing custom views
 

1. make your store your "option box" (control) value in the variable
Set your options to be xlPaperLetter and xlPaperLegal
myVar = Control.Value 'Replace Control with correct object

2. Use the variable in place of the xlPaperLetter
.PaperSize = myVar


"ExcelMonkey" wrote:

I have a macro which prints a custom view as follows in example 1:

ActiveWorkbook.CustomViews("Main Cover Sheet").Show
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

The print settings for this are set using legal paper. I want to give the
user the choice to switch to letter if they want to. I know I can do this in
code by doing the following in example 2:

ActiveWorkbook.CustomViews("Main Cover Sheet").Show
With ActiveSheet.PageSetup
.PaperSize = xlPaperLetter
End With

What I want to do is have the user choose via an option box. I want to then
pass the choice to a variable in VBA - say "papersize". How do you pass this
variable to the code I already have without repeating all the code in example
2. Can you integrate into the first line of code with a comma (i.e.
Collate:=TRUE, PaperSize: xlPaperLetter). Or do you need the second line?

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.PageSetup.PaperSize = xlPaperLetter

Thanks

EM

Setting paper size as variable for printing custom views
 

Sorry, I was a little hasty. Item one should read:

1. Store your "option box" (control) value in the variable and
Set your options to be xlPaperLetter and xlPaperLegal:
myVar = Control.Value 'Replace Control with correct object



"ExcelMonkey" wrote:

I have a macro which prints a custom view as follows in example 1:

ActiveWorkbook.CustomViews("Main Cover Sheet").Show
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

The print settings for this are set using legal paper. I want to give the
user the choice to switch to letter if they want to. I know I can do this in
code by doing the following in example 2:

ActiveWorkbook.CustomViews("Main Cover Sheet").Show
With ActiveSheet.PageSetup
.PaperSize = xlPaperLetter
End With

What I want to do is have the user choose via an option box. I want to then
pass the choice to a variable in VBA - say "papersize". How do you pass this
variable to the code I already have without repeating all the code in example
2. Can you integrate into the first line of code with a comma (i.e.
Collate:=TRUE, PaperSize: xlPaperLetter). Or do you need the second line?

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.PageSetup.PaperSize = xlPaperLetter

Thanks

EM