Select method of range class failed

[Dale Fye]

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

 

[Gary''s Student]

This seems to work without an intermediate pause:

ub dale()
Set wks = ActiveWorkbook.Sheets("ErrorReport")
wks.Visible = True
wks.Activate
wks.PageSetup.PrintArea = "$A$1:$E$13"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True,
Collate:=True
wks.Visible = False
End Sub

 

[Jim Thomlinson]

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

 

[Dale Fye]

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.

 

[Dale Fye]

Disregard.

When I added a 0 to the Show method it did exactly what I needed. Final
code looks like:

Public Sub PrintErrors()

Dim wks As Worksheet
Dim myTime As Date

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

End Sub

 

[Dale Fye]

Took it one step further and replaced:

.Range("A:E").PrintPreview
with

.Range("A:E").Select
Application.Dialogs(xlDialogPrintPreview).Show

With the previous method, if I clicked Print in the Preview mode, it went
straight to the default printer. With this latter method, clicking Print in
Preview mode actually pops up the printer dialog, allowing the user to select
what printer to print to.

Thanks for your help.

 

[Jim Thomlinson]

I don't seem to be having your problems. A few quick questions. What is the
flow of exectution. What happens and when. When is the form opened up. What
buttons are clicked and when. Is you form Modal (Check the ShowModal
property. Mine was set to false)...

 

[Dale Fye]

Jim,

The problem is that I'm an Access developer, working in Excel, and I'm not
familiar enough with the Excel Object Model to realize that the Excel Forms
object has a ShowModal property. Now that I've set it to False, everything
seems to be working better.

Am actually enjoying me foray into Excel. Just wish the object model
properties and methods for doing the same thing had the same names.

Thanks.