sujittalukde
06-12-2007, 04:10 AM
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
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