You must log in or register to reply here. Similar threads P. Question Macro to automatically print. Replies 1 Views Nov 1, pure vito. Replies 9 Views Sep 13, jolivanes. Replies 20 Views Aug 17, Alex Blakenburg. Question Combine two separate VBA codes into one. GingaNinga Sep 27, Excel Questions. Sep 27, ranman GingaNinga Sep 27, Excel Questions 2.
The classes Chart and Worksheet. The following procedures can be used to set variables of type PageSetup: Chart. PageSetup and Worksheet.
AlignMarginsHeaderFooter returns True for Excel to align the header and the footer with the margins set in the page setup options. BlackAndWhite true if elements of the document will be printed in black and white. BottomMargin returns or sets the size of the bottom margin, in points. CenterFooter center aligns the footer information in the PageSetup object.
CenterFooterPicture returns a Graphic object that represents the picture for the center section of the footer. Used to set attributes about the picture. CenterHeader center aligns the header information in the PageSetup object. CenterHeaderPicture returns a Graphic object that represents the picture for the center section of the header.
CenterHorizontally true if the sheet is centered horizontally on the page when it's printed. CenterVertically true if the sheet is centered vertically on the page when it's printed. DifferentFirstPageHeaderFooter true if a different header or footer is used on the first page.
Draft true if the sheet will be printed without graphics. I display screenshots of the print preview for clarity purposes. The printout looks materially the same. The macro prints the 2 first pages of the sample worksheet.
However, the page setup can be easily improved to, for example:. The following macro example 2 deals with these and several other common page setup settings. Let's dive right into it:. The code of this Sub procedure may look relatively complex at first glance. Don't worry about that too much. Some of the VBA constructs I include in the macro don't have a material effect on the print preview that I display at the end.
This is the case, for example, with the PageSetup. PrintQuality property I mention in step 7 below. I do this because my main purpose is to provide you examples that you can easily adjust and use.
Let's dive into some more details of this sample macro. In this case, I take you through each of the main steps the VBA code goes through. At the beginning of this VBA Tutorial, I mention that you can generally print or display the print preview of a page in 2 easy steps:.
I'm aware that the following explanation goes through many more steps. However, I organize it this way mostly for clarity purposes. At a basic level, I'm simply applying the 2 steps above. The first 3 lines of code declare 3 variables , one of these myWorksheet an object variable.
The fourth and fifth lines of code carry out assignments. This goes as follows:. Notice that there are several With… End With blocks. The purpose of these With… End With blocks is to simplify the syntax. The series of statements within each block work with the object specified in the opening line of the block. The second step is slightly more involved. At its basic level, it adds a page break every 41 rows, starting on the first row with data in the sample worksheet row 5.
These 3 lines of code use the PageSetup. PrintArea, PageSetup. PaperSize and PageSetup. Orientation properties to specify the print area, paper size and orientation. One of the main issues with the results obtained when executing macro example 1 above printInExcel is that not all the columns appeared in the same piece of paper.
The Email column didn't fit. The 3 lines of code we're working with solve this problem. They make Excel scale the worksheet to 1 page wide. Let's look at what each line of code does:. These 3 lines of code specify the rules for the printing of certain items , as follows:. As I mention in the section on the PageSetup.
PrintQuality property, you can generally get a good idea of the syntax you should use by working with the macro recorder. This is what I did to determine that the appropriate value is The exact statement you use to specify print quality may be different in your situation. Within the context of this example, it doesn't really make much difference because, at the end, we're not going to print the worksheet.
Just get a print preview. These 3 lines of code specify the margins and basic alignment of headers and footers. For purposes of these statements, remember that the variable myCmPointsBase holds the number of points equivalent to 0.
These 4 lines of code specify the general page margins. For these purposes, I rely on the myCmPointsBase variable. The value held by this variable is the number of points equivalent to 0. The statements we're looking at proceed as follows:. These 2 lines of code determine that the sheet is centered both horizontally and vertically when printing.
Both statements have the same basic structure as follows:. Within this example, several statements deal with headers and footers. Let's look at each of the different headers and footers that the macro sets:.
These 2 lines of code set footers. However, the way they work differs, as follows:. This statement makes reference to the center header. By setting the PageSetup. This With… End With block specifies a center header picture and determines several of its most important characteristics. The statements within the block work with the Graphic object that the PageSetup. CenterHeaderPicture property returns. Those statements determine the main attributes of the Graphic object , as follows:.
This statement uses the PageSetup. OddAndEvenPagesHeaderFooter to specify that header and footers differ between odd and even-numbered pages. This leads us the next group of statements. Notice that they're all within a With… End With block With.
EvenPage End With. Therefore, the statements below refer to the Page object returned by the PageSetup. EvenPage property. The effect of these statements is virtually the same as that which sets the center and right footers in general, which I describe above. In other words:. The statement structure, however, differs depending on which footer you set.
These differences are as follows:. Notice that, further above, I include lines of code that do the same to the general center headers. The statements we analyze in this section use a similar logic and structure to that which I explain then. Let's take a closer look at this:. The center footer and center header that I set for even pages is, in practice, the same as that which I specify in general. The statement we're looking at. Because of the above, this statement generates a practical difference between even and odd pages:.
After all the page settings are defined by the previous statements, this line of code gets Excel to display the print preview. If you want to print, this is usually the place where you'd work with the Print method.
Macro example 1 above can give you a rough idea of the syntax you can use for these purposes. Notice the following characteristics, caused by the sample macro pageSetupPrintPreview. You can compare some of these with the results I obtain when executing macro example 1 above. Knowing how to correctly setup and print Excel files is essential. When done appropriately, this helps you to easily share your work with others. After reading this VBA Tutorial, you have the knowledge you require to begin automating most of your printing and page setup activities.
You also have a good understanding of the main VBA constructs you can use. The following are some of these constructs we've reviewed in this post:. You've also seen 2 practical and detailed examples of VBA code that you can adjust and start using immediately.
This workbook contains the VBA code of the sample macros and the data I show in the screenshots above. Here are some of my most popular Excel Training Resources:. Apart from speaking, programming languages are no different. The more you immerse yourself in that language, the faster you will pick it up. Therefore, what most people like you need is lots of examples that you can practice.
The book contains:. When printing a document there are different zoom settings available, which only apply to printed documents. This setting is controlled by the following VBA examples. Each example is applied to a worksheet called ZoomSettings , but could also be applied to the ActiveSheet.
To keep things in proportion, it is necessary to resize the form using the Height and Width properties. If provided with a value outside the permitted range, VBA will automatically adjust the height and width UserFrom to be minimum or maximum.
This can result in UserForms which are not in the same proportions. By entering your email address you agree to receive emails from Excel Off The Grid. Do you need help adapting this to your needs? By taking the time to understand the techniques and principles in this post and elsewhere on this site you should be able to adapt it to your needs.
What next?
0コメント