Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select method of range class failed
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select method of range class failed
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 -- Gary''s Student - gsnu200755 "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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select method of range class failed
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select method of range class failed
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select method of range class failed
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 -- Don''t forget to rate the post if it was helpful! Email address is not valid. Please reply to newsgroup only. "Dale Fye" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select method of range class failed
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. -- 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select method of range class failed
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)... -- HTH... Jim Thomlinson "Dale Fye" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select method of range class failed
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. -- Don''t forget to rate the post if it was helpful! Email address is not valid. Please reply to newsgroup only. "Jim Thomlinson" wrote: 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)... -- HTH... Jim Thomlinson "Dale Fye" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Method of Range class failed | Excel Programming | |||
What did I do? (Select Method of Range Class Failed ) | Excel Programming | |||
Select method of Range class failed - but why??? | Excel Programming | |||
select method of range class failed | Excel Programming | |||
select method of range class failed | Excel Programming |