Macro Printing in Excel 2003 Crashes Excel 1

[hext2003]

I set up a macro a couple of years ago. We have to print out acct forms once per year and mail them out. This has worked fine for a couple of years. Now this year (not changing any code) it prints the first one and then gets hung up until excel completely crashes.

If I step through the code. it works just fine. Only problen is there are 2400 of these and for me to step through all 2400 is kinda a waste of time. (Just sitting holding down the "F8" key) We do have a new network printer (Copy Machine). So I am wondering if it's the printer? Do I need a pause someplace in the code. Any thoughts?

Here is the code that was working and now doesn't. It's hard to know where the problem is. If I step through the code it works fine. It's when I just release and let it run. It shows the print box, counts up the pages from 1 to 4 then just freezes. Stays that way for about 2 mins then shuts down Excel.

PrtPg5 = True
Sheets("Form").Activate
StartRow = Range("StartRow")
EndRow = Range("EndRow")


If StartRow > EndRow Then
Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
MsgBox Msg, vbCritical, APPNAME
End If

For i = StartRow To EndRow

Range("RowIndex") = i

'For Footer
acct = Worksheets("Form").Range("A308").Value
Worksheets("Form").PageSetup.CenterFooter = "&""OCR A Extended,Regular""&12" & acct

'Checks if need page 5 printed
If XI > 0 Or XX > 0 Or XH > 0 Then
PrtPg5 = True
ActiveSheet.PageSetup.PrintArea = "Print5"
Else
PrtPg5 = False
ActiveSheet.PageSetup.PrintArea = "Print4"
End If


If Range("Preview") Then
ActiveSheet.PrintPreview
Else
ActiveSheet.PrintOut
Application.ScreenUpdating = True
End If


Next i

TIA

 

[SkipVought]

hi,

Don't know if any of this may help. I think the culprit is your printer or printer driver...

[b]
'
'turn OFF ScreenUpdating 
'
    Application.ScreenUpdating = False
'
'reference the Worksheet object for ALL related object calls
'    [/b]
    With Worksheets("Form")
        For i = StartRow To EndRow
       
            .Range("RowIndex") = i
            
            'For Footer
            acct = .Range("A308").Value[b]
'
'PageSetup seems to have alot of overhead. I'd call that node ONE TIME
'
            With .PageSetup[/b]
                .CenterFooter = "&""OCR A Extended,Regular""&12" & acct
      
                'Checks if need page 5 printed
                If XI > 0 Or XX > 0 Or XH > 0 Then
                    PrtPg5 = True
                    .PrintArea = "Print5"
                Else
                    PrtPg5 = False
                    .PrintArea = "Print4"
                End If[b]
            End With[/b]
                   
            If .Range("Preview") Then
                .PrintPreview
            Else
                .PrintOut[b]
'
' why do you want ScreenUpdating.  I'd MOVE this statement!!!
'
'                Application.ScreenUpdating = True[/b]
            End If
            
         
        Next i
    End With
[b]
    Application.ScreenUpdating = True[/b]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hext2003]

My Form is on one sheet. It has a second sheet with all of the data that needs to be merged into the form.

I use Indirect to pull this data into the form. The Row Index i is what pull the data from the Data Sheet into the Form. So I need to do an update of the screen. So the print call is printing the current form with the current information. Otherwise it would print the same form over and over. (I think... it's been a while since I wrote this. I can comment it out and see what happens.)

I think it has something to do with the print driver also, but I don't know a work around as of yet.

TIA

 

[SkipVought]

Does not matter that ScreenUpdating is FALSE and you've got new data on the sheet with each pass thru the loop. The DATA is still there for each pass and WILL print.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hext2003]

Sorry, I never came back to answer this!

This worked!!! I thought I needed that Screenupdating to get the data on the screen to change. in order for the print to work.

Commented out this line. It works FINE!

Thank you SOOO MUCH!

Merry Christmas!

 

[SkipVought]

And back acha, pilgrim. ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]