As ever, you use this free program at your own risk.
Feel free to use the free software on this site but please honor the copyright.
Sometimes you may want to print several worksheets within a workbook. Of course
you could simply select each worksheet one at a time and print them, however,
using this facility you can select multiple worksheets to print and print them
in one operation.
The print facility allows a user to print multiple worksheets in one operation.
When it is correctly implemented it will show userforms which are modal and will
stay on top of the current worksheet. They can be dragged and dropped within the
window if needed.
This facility will print hidden worksheets, but will not print
very hidden worksheets.
An example of the "Print Form" is:
The frmPrint userform is coded in such a away that it cannot be closed. If you need to give it the abilty to be closed then you need to remove the UserForm_QueryClose subroutine from the frmPrint userform.
When you click the 'Print' button you will see the ListSheets userform. This looks like:
From this form you can select one or more worksheets to be printed. Use the 'ctrl' and/or 'shift' keys in the usual way to select worksheets to be printed.
The checkboxes allow you to specify if the output should be printed in landscape (the default is Portrait). It should also be noted that the print will attempt to print a worksheet on one page.
You can have the facility hide empty rows and/or columns. These will be restored after printing.
You can have the facility generate headers and footer. These will be kept after printing so you may want to delete them if they are not wanted.
After printing you will be returned to the worksheet that you were on before printing.
Demonstration of Printing Worksheets.
If you wish to see a demonstration video of printing multiple worksheets, please click the button below
A full working copy of a workbook that utilizes this technique can be found by clicking the button below.
There will also be a requirement to code a statement for when the Workbook is initially opened.
All the required coding and userforms are available by following the appropriate links below.
Enjoy!
As normal the usual disclaimers apply.
Step 1: Ensure the Workbook is Macro Enabled.
Open the target workbook. Make sure that the workbook is macro enabled. It should have an XLSM extension. If the workbook is not macro enabled, the code will not work.
Step 2: Activate the Developer Tab.
The first step is to activate the developer tab in Excel if it is not already there.
There are two ways of achieving this.
If you want to permanently have the developer tab available you can do this by following the directions in the video below.
The other method is to press "ALT"+"F11" at the same time. Remember that your keyboard may also require you to press the "fn" key also.
Either of these methods should open the "Developer" screen.
A YouTube™ video on how to activate the "Developer" tab is available by clicking on the button below.
Step 3: Download the Zip file Containing the Userforms.
The Zip file containing the Userforms ( ListSheets & frmPrint) is available by clicking the button below.
Step 4: Extract the Zip File Members.
The next thing to do is to unzip the contents of the userform zip file.
How you unzip these will depend on your preferred unzip program. We use and recommend using 7-zip.
Once this is done the files will be ready for importing into your workbook.
Step 5: Add the Required Function.
There is 1 function that is required to be coded for the facility to work. This is named ColumnEmpty
ColumnEmpty is available for download by clicking the button below.
Step 6: Add Code To Show Userform When Workbook is Opened.
|
Private Sub Workbook_Open() frmPrint.Show vbApplicationModal End Sub |
In order that the 'Print' userform (frmPrint) is shown when the workbook opens you will need to add the code alongside to the ThisWorkbook Excel Objects in the developer window. |
That is all there is to it.
This material has been used successfully by Abbydale Systems LLC. and to the best of our knowledge this material and any system(s) of which it is a part are operational as of the service level or date stated in the body of this material (if so stated). However, NO warranty is given or implied as to the accuracy of this material or any related material or systems, and no responsibility is assumed for any effect or modification directly or indirectly caused by the use of this material.
It is the responsibility of any user of this material to evaluate its usefulness to the user's environment.
Abbydale Systems LLC. does not guarantee to keep this or any related material current, nor does it guarantee to provide any corrections or extensions described by any users of this material or any corrections or extensions made in the future by Abbydale Systems LLC. itself.
