How to select range like page setup

[moonhk]

Hi All
In the Page setup dialogs, sheet tab, you can select Page Area , Rows
to report at top and Columns to repeat at left, by click the icon,
allow you select worksheet range. Is this methods can be apply on
VBA ? If yes how to apply on vba coding ?

moonhk
GMT+8

 

[Leith Ross]

Hi All
In the Page setup dialogs, sheet tab, you can select Page Area , Rows
to report at top and Columns to repeat at left, by click the icon,
allow you select worksheet range. Is this methods can be apply on
VBA ? If yes how to apply on vba coding ?

moonhk
GMT+8

Hello moonhk,

This line of code will display the PageSetup Dialog from VBA

X = Excel.Application.Dialogs(xlDialogPageSetup).Show

Sincerely,
Leith Ross

 

[broro183]

hi,

Try recording a macro where you adjust the page setup as desired, then view
& modify the resulting code (see below for an example). This will eliminate
the need for any user input which is required when the pagesetup dialog is
displayed (see Leith's suggestion).

Example code:
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

note: a lot of the resulting lines of code can be deleted from the finished
code for readability/conciseness. I'd test your recorded code on a copy of
work & keep deleting lines of code until you find that the layout changes
from what you want, undo your last change & then use that code.


hth
Rob

hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Dave Peterson]

Dim myRng as range

on error resume next
set myrng = application.inputbox(Prompt:="Select a range", type:=8)
on error goto 0

if myrng is nothing then
'user hit cancel
else
msgbox myrng.address(external:=true)
end if

 

[moonhk]

Dim myRng as range

on error resume next
set myrng = application.inputbox(Prompt:="Select a range", type:=8)
on error goto 0

if myrng is nothing then
'user hit cancel
else
msgbox myrng.address(external:=true)
end if

Thank Dave , You coding tested OK.
Thank other also.