PDA

View Full Version : Solved: Print sheet in a loop procedure



sujittalukde
07-08-2008, 12:26 AM
I am using the following code to loop through cells and print each page after looping.
If I use Activesheet.printout method it starts printing to default print so I want to show the Print dialog box using this one:


Application.Dialogs(xlDialogPrint).Show

Now where should I put the lineso that if the user
clicks Cancel, Nothing will happen.
Clicks Preview, it will show the preview only for the loop
Clicks Print or OK, it will start printing

In the entire process, Print Dialog has to be shown only once.

Code is:


Sub PrintSlips()
'Application.Dialogs(xlDialogPrint).Show
Sheets("Payslips").Select
rowscnt = Sheets("Employee Pay").UsedRange.Rows.Count
For i = 2 To rowscnt Step 2
emp = Sheets("Employee Pay").Cells(i, 1).Value
emp1 = Sheets("Employee Pay").Cells(i, 1).Offset(1, 0).Value
Sheets("Payslips").Cells(5, 3).Value = emp
Sheets("Payslips").Cells(22, 3).Value = emp1
ActiveSheet.PrintOut
Next i
End Sub

Bob Phillips
07-08-2008, 01:10 AM
Sub PrintSlips()
If Application.Dialogs(xlDialogPrint).Show then

Sheets("Payslips").Select
rowscnt = Sheets("Employee Pay").UsedRange.Rows.Count
For i = 2 To rowscnt Step 2

emp = Sheets("Employee Pay").Cells(i, 1).Value
emp1 = Sheets("Employee Pay").Cells(i, 1).Offset(1, 0).Value
Sheets("Payslips").Cells(5, 3).Value = emp
Sheets("Payslips").Cells(22, 3).Value = emp1
ActiveSheet.PrintOut
Next i
End If
End Sub

sujittalukde
07-08-2008, 01:21 AM
Thanks xld for the solution. In the mean time I have made the alterantive solution solution and the code is:


Sub PrintSlips()
res = Application.Dialogs(xlDialogPrinterSetup).Show
If res = False Then
Exit Sub
Else
Sheets("Payslips").Select
rowscnt = Sheets("Employee Pay").UsedRange.Rows.Count
For i = 2 To rowscnt Step 2
emp = Sheets("Employee Pay").Cells(i, 1).Value
emp1 = Sheets("Employee Pay").Cells(i, 1).Offset(1, 0).Value
Sheets("Payslips").Cells(5, 3).Value = emp
Sheets("Payslips").Cells(22, 3).Value = emp1
ActiveSheet.PrintOut
Next i
End If
End Sub

Bob Phillips
07-08-2008, 01:49 AM
MIne's better!

sujittalukde
07-08-2008, 01:55 AM
You are a Distinguished Lord of VBAX and I am learner and learned many from you through this forum.
I cannot go beyond you You will always remain ahead,
Thanks.

Bob Phillips
07-08-2008, 02:25 AM
Only teasing mate :whistle: