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
    wk.Calculate
 Next
 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
    Else
        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.

 

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.