print selection on 1 sheet ( landscape or portrait)

[krabistaja]

Hi.

I recorded macro to print out selection on 1 sheet. I recorded it to print out on portrait. I want macro to ask me with message box where are 2 possibilities. Portrait or Landscape

can someone help me to create such message box ?

Sub PrintFit()
ActiveSheet.PageSetup.PrintArea = Selection.Address
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

 

[Tazguy37]

You could do something like this:

    Orientation = MsgBox("Landscape (Yes) or Portrait (No)?", vbYesNoCancel, "Choose Orientation")

    MsgBox Orientation
    If Orientation = vbNo Then
        MsgBox "portrait"
    ElseIf Orientation = vbYes Then
            MsgBox "landscape"
    End If

Hope that helps!

 

[krabistaja]

deamn - im so not good in this.

can you please help me with whole macro?
i dont know, where to put the msg box and how to change this recorded part

 

[BBHRVS]

Here it is:

Sub PrintFit()

userneed = MsgBox("Orientation : Portait (vbyes), Landscape (vbno)", vbQuestion + vbYesNo, "Page orientation")
If userneed = vbYes Then
pageset = xlPortrait
Else
pageset = xlLandscape
End If

ActiveSheet.PageSetup.PrintArea = Selection.Address
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.Orientation = pageset
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

But the best (I mean the most "userfriendly") way to do it should be to use a userform.
Rgds;

 

[krabistaja]

thank you very much - it works just great

 

[krabistaja]

Hello again.

There still is one problem.

The macro actually doesnt decrease the scaling so that all the data fits to 1 sheet. Table size stays on 100% and it printes out 1 sheet with part of the table.

What i need, is, that if table in 100% doesnt fit to A4, then scaling decreases so that it does fit.

Pardon my english. Still hope youll understand.

 

[Tazguy37]

Have you tried setting the paper size to A4?

.PaperSize = xlPaperA4

 

[krabistaja]

Hello.

Yes - i included this in script - it doesnt help.
Still if table in 100% is bigger than the a4, it goes to another sheet - it doesnt fit table on 1 sheet.



help

 

[BBHRVS]

The instruction "Papersize" must be BEFORE the 2 "FitToPage" instructions.

 

[kaka4u]

Use:

.FitToPagesWide = 1
.FitToPagesTall = False