Run sub before sheet is printed?

**proepert** · 05-20-2010, 11:54 PM

I would like to force a sub routine to run whenever the File, Print command is used on a particular sheet. Is this possilbe?

Yes, I have a custom Print button on the sheet, but the users can still select the File, Print command and print bad data.

Yes I also know I can run the sub routine whenever it the sheet is activated, but the routine takes some time and I would rather not do that.

Thanks for your help.

Excel 2000

**Tellm** · 05-21-2010, 12:33 AM

Could you not create your own menu options and toolbars, so that only your print button is available.

**Huron** · 05-21-2010, 09:00 AM

You could use a BeforePrint event macro in a workbook module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Select Case ActiveSheet.Name
        Case "ParticularSheet"        
        Application.Run "YourSub"
End Select

End Sub

This is triggered when any sheet is printed. If the specified sheet is to be printed it will run your sub, otherwise it will just print as normal.

**proepert** · 05-21-2010, 12:56 PM

Thank you very much.

This works when the sheet is selected, but how can I also run the sub when the "Entire Workbook" option is selected in the Print dialog box?

**Huron** · 05-22-2010, 05:42 AM

You could prevent people from printing the entire workbook. The code below will only allow the active sheet to be printed.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
On Error GoTo 10
        Cancel = True
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
  MsgBox "Sheets can only be printed individually", _
vbInformation
Select Case ActiveSheet.Name

        Case "ParticularSheet"        
        Application.Run "YourSub"
 End Select
    ActiveSheet.PrintOut

10      Application.EnableEvents = True
        Application.ScreenUpdating = True
 
End Sub