Command button to print in excel

[dannyocean]

Hi All,

I am trying to build command buttons to print specific ranges of worksheets. Any suggestion would be appreicated.
I need to do this to help make my application idiot proof.

TIA

Danny

 

[Hasit]

If the ranges in the worksheets will not change, set the range up, and then record a macro that prints. Assign the macro to a button and add it to the menu or icon bar.

If you do not want to add it to the menu or icon bar, then create a button in excel and perform the same process as described above. You can name the button etc etc to make it "idiot" proof.

 

[DaleWatson123321]

Danny,

Just a little contribution to Hasit's suggestion. He is "on the right track", but IF the ranges are subject to change (daily or anytime in the future, which they USUALLY are), then you should ASSIGN RANGE NAMES - which in turn should be used by your VBA code.

By using Range Names, whenever a user (or yourself) inserts a row or column, Excel "internally" keeps track of the "adjusted coordinates" of the Range Names.

There can be "other" situations, for example when data is extracted, and the amount of data extracted can/will vary. In such situations, it is necessary to have code which will determine the "size" of the data extracted. When I do this, I have my code assign a Range Name (or re-define the coordinates of the same Range Name).

First, IF you are NOT used to creating Range Names, this is an EASY method:

1) Highlight the range you wish to name
2) Hold down the <Ctrl> key and hit <F3>
3) Type in the name
3) Hit <Enter>

Do NOT use Range Names that conflict with cell coordinates or numbers. For example, do NOT use &quot;A26&quot; - instead use &quot;_A26&quot;. Don't use &quot;10&quot; - instead use &quot;_10&quot;

Here's an example of code you would attach to a &quot;button&quot;, or &quot;buttons&quot; - possibly one button for EACH range. This is based on assigning the Range Names &quot;Page1&quot; and &quot;Page2&quot;.


Dim PR As Range

Sub Print_Page1()
Range(&quot;Page1&quot.Name = &quot;PR&quot;
ActiveSheet.PageSetup.PrintArea = &quot;PR&quot;
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Sub Print_Page2()
Range(&quot;Page2&quot.Name = &quot;PR&quot;
ActiveSheet.PageSetup.PrintArea = &quot;PR&quot;
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

If you happen to have various ranges to print which require DIFFERENT print settings (e.g. Portrait vs Landscape, Margin settings, etc, then the following example can be used.

Note: In case you are not aware, you CAN copy and paste the data from Tek-Tips into Excel.


Sub Print_Page2_WithSettings()
Application.ScreenUpdating = False
Range(&quot;Page2&quot.Name = &quot;PR&quot;
ActiveSheet.PageSetup.PrintArea = &quot;PR&quot;

With ActiveSheet.PageSetup
.PrintTitleRows = &quot;&quot;
.PrintTitleColumns = &quot;&quot;
.LeftHeader = &quot;&quot;
.CenterHeader = &quot;&quot;
.RightHeader = &quot;&quot;
.LeftFooter = &quot;&quot;
.CenterFooter = &quot;&quot;
.RightFooter = &quot;&quot;
.LeftMargin = Application.InchesToPoints(0.6)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

ActiveWindow.SelectedSheets.PrintOut Copies:=1
Application.ScreenUpdating = True

' NOTE: Use the following IF you want to
' first PREVIEW the page.
'ActiveWindow.SelectedSheets.PrintPreview


I hope these examples help. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca