Solved: Print macro

[sujittalukde]

I have a macro which disables print features from excel


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

I have another macro to print which I want that print should take place if run the following macro


Sub printall()
Dim wsAtt As Worksheet
Dim wsPay As Worksheet
Dim rng As Range
Dim cl As Range

Application.Dialogs(xlDialogPrinterSetup).Show

On Error GoTo PrintAll_Error
Application.ScreenUpdating = False

Set wsAtt = ThisWorkbook.Worksheets("Register")
wsAtt.Select
Set rng = wsAtt.Range(Cells(8, 1), Cells(Rows.Count, 1).End(xlUp))
Set wsPay = ThisWorkbook.Worksheets("Payslips")

For Each cl In rng

If Not IsEmpty(cl) Then
wsPay.Cells(6, 2).Value = cl.Offset(0, 1).Value
wsPay.PrintOut 'change to Printpreview
End If
Next cl
wsPay.Select
Application.ScreenUpdating = True

Set wsAtt = Nothing
Set wsPay = Nothing
Set rng = Nothing
Set cl = Nothing

On Error GoTo 0
Exit Sub

PrintAll_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PrintAll of Module Module1"


End Sub


1.How these two code can be incorporated so that users cannot take print from normal print feature of excel but can take printuot from this macro?

2. if you run the second code as in this form, you will get a pop up list of printers. but if the user press "cancel" or "ok", it starts printing. I want that if the user clicks "cancel" it shall not start printing, print shall start only if the user clicks "OK".

Thanks for any help

[Bob Phillips]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Not PrintOK Then Cancel = True
End Sub




Public PrintOK As Boolean

Sub printall()
Dim wsAtt As Worksheet
Dim wsPay As Worksheet
Dim rng As Range
Dim cl As Range
Dim ans

ans = Application.Dialogs(xlDialogPrinterSetup).Show

If Not ans = False Then
On Error GoTo PrintAll_Error
Application.ScreenUpdating = False


Set wsAtt = ThisWorkbook.Worksheets("Register")
wsAtt.Select
Set rng = wsAtt.Range(Cells(8, 1), Cells(Rows.Count, 1).End(xlUp))
Set wsPay = ThisWorkbook.Worksheets("Payslips")

For Each cl In rng

If Not IsEmpty(cl) Then
wsPay.Cells(6, 2).Value = cl.Offset(0, 1).Value
PrintOK = True
wsPay.PrintOut 'change to Printpreview
PrintOK = False
End If
Next cl
wsPay.Select
Application.ScreenUpdating = True

Set wsAtt = Nothing
Set wsPay = Nothing
Set rng = Nothing
Set cl = Nothing

On Error GoTo 0
End If
Exit Sub

PrintAll_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PrintAll of Module Module1"


End Sub

[shasur]

Make sure that when the cancel is pressed none of the code given below ie executed

If Application.Dialogs(xlDialogPrinterSetup).Show = False Then Exit Sub

[sujittalukde]

Many thanks to duo. Code is fully working as desired