Printing from within a loop

[Infodump]

Background
I am new to VBA (as you will probably soon surmise), so maybe this is not exactly the right forum for me. I am working on a little project at work using Excel 2000 and its budled version of VBA (I work for a huge government organization that prides itself on its electronic office infrastructure and still only supplies employees with Office Small Business Edition).

The Project
I have produced a workbook with 2 worksheets. Worksheets 1 allows the user to input rows of data into a multicolumn table. When a command button is clicked a macro runs a while loop, each iteration copies the contents of a row into lables on Worksheet 2 and then prints off Worksheets 2 to produce a form used in our filing. That is the theory anyway.

The Code (simplified to copy only one item per row of data)

Private Sub CommandButton1_Click()

Dim counter As Integer
counter = 1

While Len(Worksheets("sheet1" ).Cells(counter, 1)) > 0

Worksheets("sheet2" ).Label1.Caption = Worksheets("sheet1" ).Cells counter, 1)

Worksheets("sheet2" ).PrintOut copies:=1

counter = counter + 1

Wend

End Sub

The Problem
My macro actually produces "x" copies of Worksheet 2 filled in with the contents of the first row of data from Worksheet 1, where "x " is the number of rows of data.

So, if Worksheet1 contained three rows of data (A1 to A3) each holding a single name; ie: Albert, Bernard and Charles, the output would be three copies of Worksheet 2, each with the name Albert printed on it.

By setting up a MsgBox to monitor the contents of Label1 during the execution of the loop, I can see that the label is getting updated with each iteration. However, it seems that the information being sent to the printer is not getting updated.

The Solution?
If anyone can please supply me with the cause of this problem and some sort of solution (my copy of Excel does not have acess to the Database functions,etc) I would be very grateful .

 

[VBAjedi]

Two things I would try:

First, try activating Sheet2 before you print it. That may update it. Insert the following line before the print line:

Worksheets("sheet2" ).Activate

If that doesn't work, try putting this at the beginning of the procedure:

Application.ScreenUpdating = True

Let us know if either of these work for you!

VBAjedi

 

[Loomah]

A couple more things to think about but I've just had an XL failure so I'm going now

You have a typo in your posted code in the first line inside the loop (should be "...cells(counter..." etc)

You only ever refer to one label (Label1, same line). If you have many labels name them sequentially (eg lblOutput1, lblOutput2 etc) then you can refer to them using you counter variable like this
Labels("lblOutput"&counter)

I don't understand how you are getting only the first line of data at the end of the loop but if you only want to print out the finished article move the print statement outside the loop.

Sorry I can't give a fuller answer as I was unable to recreate your problem but I hope this will give you a couple more things to consider!

Good Luck
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Infodump]

Many thanks VBAjedi and Loomah.

That is a typo in the posted code (soz), it took me several squints to spot it when you pointed it out, I really am a lazy reader and don't possess true coder's eyes (thank 'eavens for the debugger).

I tried "Worksheets("sheet2" ).Activate" and "Application.ScreenUpdating = True" to no avail. I've actually now rewritten my printing routine to 'copy', 'update', 'print' and 'delete' my worksheets that I formated as my output forms. This causes the data to be correctly printed out.

No doubt there is some easier, less clunky method of acheiving my desired result (I want to avoid print with user forms as that is dependent on the users' screen resolution), but I'll have to undergo further trials to imporve my grasp of VBA.

My solution has created a new problem; to whit, deleting the copied files automatically throws up a message box asking the user if they want to delete the worksheet or not. I don't want to trouble my users with this descision. Is there some method of cancelling this message or producing an automatic affirmative to get ride of the box?

 

[Loomah]

Hi
application.displayalerts=false/true
will do the trick!
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Infodump]

Thanks again Loomah. I am now in a position to both rock and indeed roll. Thanks also VBAjedi.