Jim,

that worked great, except that the preview form was behind the userform. So
I inserted a couple of lines in my subroutine which appears to have caused
some problems.

Public Sub PrintErrors()

Dim wks As Worksheet
Dim myTime As Date

With Sheets("ErrorReport")
.Visible = xlSheetVisible
frmMyForm.Hide
.Range("A:E").PrintPreview
frmMyForm.Show
.Visible = xlSheetHidden
End With

End Sub

This works fine right down to the frmMyForm.Show event, which seems to cause
the code in the subroutine to end.

BTW, I'm using Excel 2003, and this subroutine is being called from a custom
popup command bar. The way I knew it was causing my code to terminate early
is that before I added the Hide/Show lines of code the popup command button
would go into a down state then go back to up. When I added the Show line,
the state of that button never goes back to Up (therefore it stays checked
and cannot be executed again).

Any ideas about how to make the PrintPreview come to the forefront, or to
hide the userform without causing this problem.

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"Jim Thomlinson" wrote:

Before you can select a range you have to select the sheet. That being said
for what you wnat to do there is no need to do any selecting...

With Sheets("ErrorReport")
.Visible = xlSheetVisible
.Range("A:E").PrintPreview 'Change to PrintOut
.Visible = xlSheetHidden
End With
--
HTH...

Jim Thomlinson


"Dale Fye" wrote:

I've got a function that is supposed to select a range of cells in a
spreadsheet and run the print preview that selection. But I keep getting
error 1004 with the above message, although I only seem to get this message
when there is already a selection in the applicable sheet.

Any ideas how to avoid this?

Set wks = ActiveWorkbook.Sheets("ErrorReport")
wks.Visible = True

'pause briefly to make sure the worksheet is visible
myTime = Now
While Now - myTime < (1 / 24 / 60 / 60): DoEvents: Wend

wks.Range("A:E").Select
Selection.PrintOut Copies:=1, Preview:=True, Collate:=True
wks.Visible = False

--


Email address is not valid.
Please reply to newsgroup only.