Updating and Printing from within a Loop

[Euan]

Background
I am new to VB (as you will probably soon surmise) . 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 = 2 'the first row on Worksheet1 contains the table headers

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 (A2 to A4) each holding a simgle name; ie: Albert, Bernard and Charles, the output would be three copies of Worksheet 2, each with the name Albert printed on it.

Evidently, Worksheet 2 is not getting updated between print requests.

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 :D .

 

[TommyGun]

Okay, this hasn't been tested, but see if it helps any.

Private Sub CommandButton1_Click() 

Dim counter As Integer 

counter = 2 'the first row on Worksheet1 contains the table headers 

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

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

DoEvents

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

counter = counter + 1 

Wend 

End Sub

 

[Euan]

Thankyou, Tommygun, unfortunately, the DoEvent command did not kickstart my micro. It still continues to to print "x" copies of "Albert".

 

[Euan]

I really should pay more attention to my tying. That should have read "DoEvents" and "macro".

 

[Egress1]

Euan,

Just a thought I do something simular at work but what I do is have the macro to print out my forms on the sheet that is the form itself. I pull the info from sheet one as you do but I stay on the form sheet2 as the active sheet (the sheet that is actually printed)

 

[Euan]

Ta, Ken, I'll give it a go. I am open to any suggestions. :D