Stop printing if user selects cancel

[cdchapman]

Hi all,

I have a spreadsheet at work where users can print a worksheet by pressing a button on a userform. However, the users have now asked for an option of cancelling the print if they feel it is taking too long to be sent to the network printer. In other words at any time between when the data starts to be spooled to the printer and when the print job is completed. Is there any way this could be done in VBA?

At the moment, I've added an interim solution via the BeforePrint event, where a message box appears asking the user whether they want to start or cancel the printing. If they select cancel, then printing stops before the data even gets sent to the printer, but it would be great if there is a way of doing the above?

Any help is greatly appreciated.

Chris

 

[sgmpatnaik]

@cdchapman

HI

Please try the below code hope it will solve your problem

Private Sub CommandButton1_Click()
Dim rngToPrint As Range
     'UserForm1.Hide
         Set rngToPrint = Sheets("Sheet1").Range("j85:s130")
    rngToPrint.PrintPreview
    Rng = MsgBox("Do You Want to Print ", vbOKCancel, "Print Preview")
    If Rng = vbOK Then
        rngToPrint.PrintPreview
         rngToPrint.PrintOut
                  End If
End Sub

if not then please inform us. and it is better if you were used any code then give here we will check that

Thanks

Patnaik

 

[cdchapman]

@sgmpatnaik

Thanks for your reply. However, this is not quite what the users want. They want to be able to stop the print after they've pressed the print button (ie after the line of code rngToPrint.PrintOut in your code above) has been executed, but before the whole sheet has been printed on the printer - in other words, stopping the print job after it's been sent to the network printer.

Chris

 

[cdchapman]

Sorry, forgot to add the existing code. The code below is run when the user clicks the print button on the userform:

Private Sub PRINTCommandbutton_Click()
'Formats and initiates the printout

   'Display the Print Control form to give users a chance to stop the printing
   GL_ABORT_PRINTING = False
   [COLOR=#ff0000]PRNTSAVECONTROLForm.Caption = "Start Printing?"
   PRNTSAVECONTROLForm.PRNTSAVEMESSAGELabel.Caption = "PLEASE CONFIRM Do you wish to start printing?"
   PRNTSAVECONTROLForm.CONTINUEPRINTCommandbutton.Caption = "Start Printing"
   PRNTSAVECONTROLForm.Show

[/COLOR]   If Not GL_ABORT_PRINTING Then             'User has selected to start printing
      [COLOR=#008000]'Set Status
      Call SetStatus("PS","W","Please wait - Formatting Data to be printed.")
      'Get the data required according to what has been selected
      Call Get_Print_Data
      'Format the data
      Call Format_Data
      'Print the data on the printer selected in the PRNTRCombobox
      GL_WSPRINT.Printout Copies:=1, ActivePrinter:=PRNTSAVEFORM.PRNTRCombobox.Text
      PRNTSAVEForm.Hide
      'Set the status and confirm to user
      Call SetStatus("PS","R","Ready")
[/COLOR]      Msgbox "Data sent to printer " & PRNTSAVEForm.PRNTRCombobox.Text & ". Please collect the printout.",vbOKOnly + vbINFORMATION + vbApplication Modal,"Data Printed"
      GoTo FINISH
   End If

   If GL_ABORT_PRINTING Then                 'user has selected to abort the printing
      Msgbox "Printing hsd been aborted.", vbokonly +vbinformation + vbapplicationmodal, "Printing Aborted"
   End If

FINISH:
   'In either case redisplay the splash sheet
   GL_WSSPLASH.Select

End If
[\Code]

The code highlighted in Red is the existing code which gives users a chance to start the printing or abort it.What the users now require is the ability to abort the printing at anytime whilst the code highlighted in green above is being executed.

Hope I've explained this in enough detail...