Showing posts with label printing. Show all posts
Showing posts with label printing. Show all posts

03 April, 2022

Workbook Events: Printing - Speach - Alerts

When printing in Excel, one quite frequently makes mistakes. Sometimes the printout does not look as expected and we may waste more paper than necessary. To reduce such outcomes to minimum we can utilize Excel event feature called BeforePrint.

You can use the following workbook event procedure (VBA code) that will - just before printing your worksheet/selection - alert you with speech feature by asking if you are sure that your workbook and the print settings are OK; if not, printing is cancelled.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 For Each wk In Worksheets  'Make sure that worksheets are recalculated before printing
 vbOption = MsgBox("Are you sure that all settings are OK and print can be started?", vbYesNo)
 If vbOption = 7 Then  
'6=Yes, 7=No
    Cancel = True
    Application.Speech.Speak "Print is cancelled."
'    MsgBox ("Print is cancelled.") 'optional
        Application.Speech.Speak "Recalculation is completed and now printing takes place."
    End If

End Sub

To implement this procedure, select the Developer tab in the ribbon and select Visual Basic from the menu, then select the View tab>Project Explorer. In VBAProject of your workbook click on ThisWorkbook, then copy the code provided here and paste it into the space located directly under the Workbook field there. Save your workbook as Excel Macro-Enabled Workbook.

Try to print something to see if the event procedure works as intended.


25 October, 2021

Header/Footer: What can be entered there?

Excel offers the following built-in headers and footers. They can be added to your worksheet easily, with a single mouse click.

However, we're not limited to these options. In fact, you can enter any text/string into your worksheet cell and add it to any part of the header or footer with just one line of VBA code of a macro. E.g., this line will add contents of cell B2 to the left part of a header:


Such a line can be included in the VBA code I'm presenting at the bottom of this post.

20 May, 2021

Printing multiple workbook ranges on one page

Sometimes we may need to print couple of areas located in separate ranges of our worksheet or on different worksheets in our workbook. The reason? This way we could simply save couple of paper sheets. Also, such combining of separate ranges on one page might be useful, or even necessary, for comparison / presentation purposes, etc.

So, how could we do that?

The easiest way, I'm using, is to employ the Excel Camera tool. The tool might not be available by default on your Quick Access Excel toolbar (top-left corner). If so, you can add it there by choosing Toolbar>Customize>More Commands...  Scroll through the list of Commands to find Camera and drag it to the toolbar. From now on it'll be always ready for you to use.

Here's how to use it, e.g. for printing: