print selection on 1 sheet ( landscape or portrait)

krabistaja

New Member
Joined
Jun 18, 2004
Messages
33
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You could do something like this:

Code:
    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!
 
Upvote 0
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 :(
 
Upvote 0
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;
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,498
Messages
6,136,994
Members
450,037
Latest member
Tao86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top