Loop & Print

[John Smith]

Hi,
I have a workbook with a sheet (“Data Sheet”) that contains
approximately 50,000 rows of data and extends out to column AC. Each
row contains a unique data record, but many of the columns on each row
are blank. A second sheet (“Summary”) contains a user form with five
list boxes where the User can select criteria (Supervisor, Worker,
Work Area, Work Location, and Job Number) to search data sheet. After
the User selects the search criteria, the data is manipulated and then
presented in a pre-formatted section of the summary page, only one
record at a time, and then printed out. My question is, how do I get
the pre-formatted section of the summary page to print for each record
without any User intervention? So, if I find that a supervisor has 21
workers that worked on a specific job number, how can I get each
individual record to print consecutively? I have no idea where to
start on this one. Thanks.
James

 

[Tim Williams]

What code do you have so far?

Tim

 

[John Smith]

What code do you have so far?

Tim



- Show quoted text -

Right now, I don't have any code. I tried the macro recorder and
didn't get anything usable. I've been trying some of Ron de bruin's
code (http://www.rondebruin.nl/print.htm) but I can't seem to get
anything to work correctly - I still have to select and print manually.

 

[isabelle]

hi James,

can you show us the macro who doing realise this part:

"After the User selects the search criteria, the data is manipulated
and then presented in a pre-formatted section of the summary page,
only one record at a time, and then printed out"

 

[isabelle]

or what is the manipulation (or method you used) to doing the presentation in the summary page ?

 

[John Smith]

hi James,

can you show us the macro who doing realise this part:

"After the User selects the search criteria, the data is manipulated
and then presented in a pre-formatted section of the summary page,
only one record at a time, and then printed out"

--
isabelle

Le 2012-01-11 20:57, John Smith a écrit :




- Show quoted text -

The data is compiled using cell formulas and then stored in a range on
the worksheeet. The userform simply requests what record data needs to
be displayed on the summary page. If the request returns 9 records
then I need to fill the pre-formatted section of the summary page with
the first record, print it, and then repeat the process for the
remaing 8 records. I need to figure out how to automate the loop
through the print cycle.

 

[isabelle]

ok, you can add a CommandButton with this code,

Private Sub CommandButton1_Click()
Dim i As Integer
Sheets("Summary").ListBox1.ListIndex = -1
For i = 0 To Sheets("Summary").ListBox1.ListCount - 1
Sheets("summary").ListBox1.ListIndex = i
Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub


--
isabelle




Le 2012-01-11 22:20, John Smith a écrit :

 

[John Smith]

ok, you can add a CommandButton with this code,

Private Sub CommandButton1_Click()
Dim i As Integer
Sheets("Summary").ListBox1.ListIndex = -1
For i = 0 To Sheets("Summary").ListBox1.ListCount - 1
     Sheets("summary").ListBox1.ListIndex = i
     Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub

--
isabelle

Le 2012-01-11 22:20, John Smith a crit :




- Show quoted text -

Thanks, Isabelle, but I keep getting a run-time error 438 on this
block of code.

Private Sub CommandButton1_Click()

 

[Tim Williams]

You don't have any code at all, or just no code for the printing?

Tim

 

[John Smith]

You don't have any code at all, or just no code for the printing?

Tim





- Show quoted text -

I don't have the print routine, but the part that loads the supervisor
is:

Private Sub UserForm_Initialize()
Dim R As Long
For R = 2 To Sheets("Data Sheet").Cells(65000, 1).End(xlUp).Row
If Sheets("Data Sheet").Cells(R, 1).Value <> " " Then
ListBox1.AddItem Sheets("Data Sheet").Cells(R, 1).Value
End If
Next

 

[isabelle]

What are the names of the "listbox" on Summary sheet ?

--
isabelle



Le 2012-01-12 10:33, John Smith a écrit :

 

[John Smith]

What are the names of the "listbox" on Summary sheet ?

--
isabelle

Le 2012-01-12 10:33, John Smith a écrit :





- Show quoted text -

I don't have them named, I was planning on passing the value directly
to the cell. Do the cells have to be named ranges?

 

[isabelle]

James, do a right click to the listbox and a click on view code and tell me what you see

 

[John Smith]

James, do a right click to the listbox and a click on view code and tell me what you see

--
isabelle

Le 2012-01-12 15:24, John Smith a écrit :




- Show quoted text -

Private Sub CommandButton1_Click()
Dim i As Integer
Sheets("Student Profile").ListBox1.ListIndex = -1 'Error 438
For i = 0 To Sheets("Student Profile").ListBox1.ListCount - 1
Sheets("Student Profile").ListBox1.ListIndex = i
Sheets("Student Profile").PrintPreview 'test with PrintPreview
then change for PrintOut for a real print
Next

End Sub
Private Sub UserForm_Initialize()
Dim R As Long
For R = 2 To Sheets("DataLookupSheet").Cells(65000, 1).End(xlUp).Row
If Sheets("DataLookupSheet").Cells(R, 1).Value <> " " Then
ListBox1.AddItem Sheets("DataLookupSheet").Cells(R, 1).Value
End If
Next
End Sub

 

[isabelle]

ok, the listbox are not on a sheet but on a userform.
place the commandbutton on the userform
and change the code by this one

Private Sub CommandButton1_Click()
Dim i As Integer
UserForm1.ListBox1.ListIndex = -1
For i = 0 To UserForm1.ListBox1.ListCount - 1
UserForm1.ListBox1.ListIndex = i
Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub

 

[John Smith]

ok, the listbox are not on a sheet but on a userform.
place the commandbutton on the userform
and change the code by this one

Private Sub CommandButton1_Click()
Dim i As Integer
UserForm1.ListBox1.ListIndex = -1
For i = 0 To UserForm1.ListBox1.ListCount - 1
     UserForm1.ListBox1.ListIndex = i
     Sheets("summary").PrintPreview 'test with PrintPreview then change for PrintOut for a real print
Next
End Sub

Thanks, Isabelle. It seemed to work, but I couldn't get out of Excel -
I couldn't use the 'Esc' key or 'X' out of it, I had to use Task
Manager to kill Excel. I then got a message box that popped up and
said,"Automation error" "Catastrophic error". When I clicked on the
"OK" button, it gave me an "Out of memory" error. Any idea as to what
caused it and how to fix it?
Then I was curious about putting the controls directly on the sheet
instead of the user form. Is there a preferred method or are they both
about the same? Thanks.
James

 

[isabelle]

James, can you put your file on cjoint.com or other

 

[John Smith]

James, can you put your file on cjoint.com or other

--
isabelle

Le 2012-01-13 09:08, John Smith a écrit :






- Show quoted text -

Sorry, Isabelle, but I can't get permission to post it, but I do
believe I have solved that problem (I had some bad code in another
module). After giving this a little more thought, I think I will
gather the information from the user form, run it against an advanced
filter on another sheet, then populate the summary sheet. Does that
sound like a reasonable way of doing it or can you think of an easier
way.