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

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

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