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 facilty you
can select multiple worksheets to print and print them in one operation.
A full working copy of a workbook that utilises this technique can be found
here
In order to implement this process you will need to have 2 Userforms and 1 Function defined.
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.
There is 1 function that is required to be coded for the facility to work. This is named
ColumnEmpty
ColumnEmpty is available for download from the link on the left. It is
used to identify if a worksheet column is empty or not.
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.
Demonstration of Printing Worksheets
Alongside is a video demonstration of our free working example of printing worksheets
The full working example of this workbook is available from
here
Usage Instructions
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.
That is all there is to it.
If you need any support or assistance with any of the code on this site or if you would like to contact us, please click here
