Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
I've posted this problem in some C# groups, but after a couple of days, I've
not gotten any useful responses. So, here goes again. I have a C# application from which I need to open an Excel (2003 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. I have no hidden rows or columns. I have no comments. (these facts are so I don't get pointed to unapplicable Knowledge Base articles). Whenever I attempt to insert a row, I get a "cannot shift objects off sheet" error, even though all of the columns are visible and within the print area and there are 8 blank rows visible and in the print area. Can anyone lend a hand in figuring this out? I'm really at wits end on this, as this is the only thing keeping me from completing my project, and I've been at it for a number of days now. Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
Can I assume that you get the same error message when you insert rows
manually? Do you have any objects on the sheet? (Press F5, select Special, click Objects and click OK). If you have some they will be selected. Press Del to delete them (you're not going to save this so go ahead). Still have the problem? This will not select cell comments, which is a leading cause of this problem. But then you don't have any so no matter. -- Jim "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... | I've posted this problem in some C# groups, but after a couple of days, I've | not gotten any useful responses. So, here goes again. | | I have a C# application from which I need to open an Excel (2003 | 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. | I have no hidden rows or columns. I have no comments. (these facts are so | I don't get pointed to unapplicable Knowledge Base articles). Whenever I | attempt to insert a row, I get a "cannot shift objects off sheet" error, | even though all of the columns are visible and within the print area and | there are 8 blank rows visible and in the print area. Can anyone lend a | hand in figuring this out? | | I'm really at wits end on this, as this is the only thing keeping me from | completing my project, and I've been at it for a number of days now. | | Tim | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
Hi Tim,
Should have hit enter so I think the only thing here not already mentioned is protection. Check your lastcell location Ctrl+End Make sure you are not trying to insert a very large number of rows like the entire worksheet, half the worksheet. You did not mention anything about not having protection turned on. Do you have shapes on the workbook. Can you manually insert rows in the worksheet. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... I've posted this problem in some C# groups, but after a couple of days, I've not gotten any useful responses. So, here goes again. I have a C# application from which I need to open an Excel (2003 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. I have no hidden rows or columns. I have no comments. (these facts are so I don't get pointed to unapplicable Knowledge Base articles). Whenever I attempt to insert a row, I get a "cannot shift objects off sheet" error, even though all of the columns are visible and within the print area and there are 8 blank rows visible and in the print area. Can anyone lend a hand in figuring this out? I'm really at wits end on this, as this is the only thing keeping me from completing my project, and I've been at it for a number of days now. Tim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
When you do Ctrl+End (or EditGo-ToSpecialLastCell) where does the active
cell end up? Row 65,536? That's why it isn't allowing inserts (Excel has a limit of 65,536 rows and 255 columns) Excel sometimes considers a lot more of a worksheet to be "in use" than might be apparent to the naked eye. This can be due to indescriminate formatting, etc. In any case, highlight "your" 1st empty row through row 65536, right click & select "Delete". The Delete key will *not* do what you want, it will only "clear cell contents" and won't change what Excel considers to be the "used range". Do the same thing for any "empty" columns that you and Excel disagree on. Save the file, close & reopen. Do Ctrl+End again until it is what you expect. (depending on your version the close & reopen may not be necessary). HTH, -- George Nicholson Remove 'Junk' from return address. "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... I've posted this problem in some C# groups, but after a couple of days, I've not gotten any useful responses. So, here goes again. I have a C# application from which I need to open an Excel (2003 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. I have no hidden rows or columns. I have no comments. (these facts are so I don't get pointed to unapplicable Knowledge Base articles). Whenever I attempt to insert a row, I get a "cannot shift objects off sheet" error, even though all of the columns are visible and within the print area and there are 8 blank rows visible and in the print area. Can anyone lend a hand in figuring this out? I'm really at wits end on this, as this is the only thing keeping me from completing my project, and I've been at it for a number of days now. Tim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
Jim:
No, I can manually insert. I have no special objects. I still get the error. Tim "Jim Rech" wrote in message ... Can I assume that you get the same error message when you insert rows manually? Do you have any objects on the sheet? (Press F5, select Special, click Objects and click OK). If you have some they will be selected. Press Del to delete them (you're not going to save this so go ahead). Still have the problem? This will not select cell comments, which is a leading cause of this problem. But then you don't have any so no matter. -- Jim "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... | I've posted this problem in some C# groups, but after a couple of days, I've | not gotten any useful responses. So, here goes again. | | I have a C# application from which I need to open an Excel (2003 | 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. | I have no hidden rows or columns. I have no comments. (these facts are so | I don't get pointed to unapplicable Knowledge Base articles). Whenever I | attempt to insert a row, I get a "cannot shift objects off sheet" error, | even though all of the columns are visible and within the print area and | there are 8 blank rows visible and in the print area. Can anyone lend a | hand in figuring this out? | | I'm really at wits end on this, as this is the only thing keeping me from | completing my project, and I've been at it for a number of days now. | | Tim | | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
David:
I don't have any protection on. I'm only trying to insert one row (though, eventually I'll need to insert more rows). I have no shapes. I can manually insert rows. Tim "David McRitchie" wrote in message ... Hi Tim, Should have hit enter so I think the only thing here not already mentioned is protection. Check your lastcell location Ctrl+End Make sure you are not trying to insert a very large number of rows like the entire worksheet, half the worksheet. You did not mention anything about not having protection turned on. Do you have shapes on the workbook. Can you manually insert rows in the worksheet. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... I've posted this problem in some C# groups, but after a couple of days, I've not gotten any useful responses. So, here goes again. I have a C# application from which I need to open an Excel (2003 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. I have no hidden rows or columns. I have no comments. (these facts are so I don't get pointed to unapplicable Knowledge Base articles). Whenever I attempt to insert a row, I get a "cannot shift objects off sheet" error, even though all of the columns are visible and within the print area and there are 8 blank rows visible and in the print area. Can anyone lend a hand in figuring this out? I'm really at wits end on this, as this is the only thing keeping me from completing my project, and I've been at it for a number of days now. Tim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
If I Ctrl+End, the active cell is the last cell of what I entered. I
already did the "clear" trick; no luck. Tim "George Nicholson" wrote in message ... When you do Ctrl+End (or EditGo-ToSpecialLastCell) where does the active cell end up? Row 65,536? That's why it isn't allowing inserts (Excel has a limit of 65,536 rows and 255 columns) Excel sometimes considers a lot more of a worksheet to be "in use" than might be apparent to the naked eye. This can be due to indescriminate formatting, etc. In any case, highlight "your" 1st empty row through row 65536, right click & select "Delete". The Delete key will *not* do what you want, it will only "clear cell contents" and won't change what Excel considers to be the "used range". Do the same thing for any "empty" columns that you and Excel disagree on. Save the file, close & reopen. Do Ctrl+End again until it is what you expect. (depending on your version the close & reopen may not be necessary). HTH, -- George Nicholson Remove 'Junk' from return address. "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... I've posted this problem in some C# groups, but after a couple of days, I've not gotten any useful responses. So, here goes again. I have a C# application from which I need to open an Excel (2003 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. I have no hidden rows or columns. I have no comments. (these facts are so I don't get pointed to unapplicable Knowledge Base articles). Whenever I attempt to insert a row, I get a "cannot shift objects off sheet" error, even though all of the columns are visible and within the print area and there are 8 blank rows visible and in the print area. Can anyone lend a hand in figuring this out? I'm really at wits end on this, as this is the only thing keeping me from completing my project, and I've been at it for a number of days now. Tim |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
If you don't get the error message when inserting manually, but do get it
when inserting via code, then the code becomes prime suspect. You might consider posting it. -- George Nicholson Remove 'Junk' from return address. "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... Jim: No, I can manually insert. I have no special objects. I still get the error. Tim "Jim Rech" wrote in message ... Can I assume that you get the same error message when you insert rows manually? Do you have any objects on the sheet? (Press F5, select Special, click Objects and click OK). If you have some they will be selected. Press Del to delete them (you're not going to save this so go ahead). Still have the problem? This will not select cell comments, which is a leading cause of this problem. But then you don't have any so no matter. -- Jim "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... | I've posted this problem in some C# groups, but after a couple of days, I've | not gotten any useful responses. So, here goes again. | | I have a C# application from which I need to open an Excel (2003 | 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. | I have no hidden rows or columns. I have no comments. (these facts are so | I don't get pointed to unapplicable Knowledge Base articles). Whenever I | attempt to insert a row, I get a "cannot shift objects off sheet" error, | even though all of the columns are visible and within the print area and | there are 8 blank rows visible and in the print area. Can anyone lend a | hand in figuring this out? | | I'm really at wits end on this, as this is the only thing keeping me from | completing my project, and I've been at it for a number of days now. | | Tim | | |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
Here is my code, boiled down to the pertinent parts:
<snippet Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); // open the template Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(_sTimesheetTemplate, System.Type.Missing, false, System.Type.Missing, System.Type.Missing, System.Type.Missing, true, System.Type.Missing, System.Type.Missing, true, System.Type.Missing, System.Type.Missing, false, System.Type.Missing, System.Type.Missing); if( workbook != null ) { // get reference to the worksheet Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Ac tiveSheet; // insert a row after the current row (iRow starts where the first line item should go and increments for additional rows) sheet.Rows.Insert( Microsoft.Office.Interop.Excel.XlDirection.xlUp, iRow ) </snippet As you can see, nothing exotic is happening. Tim "George Nicholson" wrote in message ... If you don't get the error message when inserting manually, but do get it when inserting via code, then the code becomes prime suspect. You might consider posting it. -- George Nicholson Remove 'Junk' from return address. "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... Jim: No, I can manually insert. I have no special objects. I still get the error. Tim "Jim Rech" wrote in message ... Can I assume that you get the same error message when you insert rows manually? Do you have any objects on the sheet? (Press F5, select Special, click Objects and click OK). If you have some they will be selected. Press Del to delete them (you're not going to save this so go ahead). Still have the problem? This will not select cell comments, which is a leading cause of this problem. But then you don't have any so no matter. -- Jim "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... | I've posted this problem in some C# groups, but after a couple of days, I've | not gotten any useful responses. So, here goes again. | | I have a C# application from which I need to open an Excel (2003 | 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. | I have no hidden rows or columns. I have no comments. (these facts are so | I don't get pointed to unapplicable Knowledge Base articles). Whenever I | attempt to insert a row, I get a "cannot shift objects off sheet" error, | even though all of the columns are visible and within the print area and | there are 8 blank rows visible and in the print area. Can anyone lend a | hand in figuring this out? | | I'm really at wits end on this, as this is the only thing keeping me from | completing my project, and I've been at it for a number of days now. | | Tim | | |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
cannot shift objects off sheet
I'm not that familiar with .Net but in context of an Insert
"XlDirection.xlUp" is jumping up and sticking out its tongue at me (i.e., it simply looks wrong). "Up" might be an OK "shift direction" command when Deleting, but not when Inserting. Example: if you are on row 2 and tried to insert a row "up" where would you expect row 1 to go? Off the sheet? As I recall that's pretty much what your original error message told you it couldn't do. The Excel VBA Help entry for the Insert method says the *valid* xlInsertShiftDirection constant options for use with the Insert method are xlShiftToRight or xlShiftDown. In the context of Inserting a Row, only xlShiftDown would seem to make any sense. I can only make an educated guess that the equivalent via Interop is xlDown. HTH, -- George Nicholson Remove 'Junk' from return address. "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... Here is my code, boiled down to the pertinent parts: <snippet Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); // open the template Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(_sTimesheetTemplate, System.Type.Missing, false, System.Type.Missing, System.Type.Missing, System.Type.Missing, true, System.Type.Missing, System.Type.Missing, true, System.Type.Missing, System.Type.Missing, false, System.Type.Missing, System.Type.Missing); if( workbook != null ) { // get reference to the worksheet Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Ac tiveSheet; // insert a row after the current row (iRow starts where the first line item should go and increments for additional rows) sheet.Rows.Insert( Microsoft.Office.Interop.Excel.XlDirection.xlUp, iRow ) </snippet As you can see, nothing exotic is happening. Tim "George Nicholson" wrote in message ... If you don't get the error message when inserting manually, but do get it when inserting via code, then the code becomes prime suspect. You might consider posting it. -- George Nicholson Remove 'Junk' from return address. "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... Jim: No, I can manually insert. I have no special objects. I still get the error. Tim "Jim Rech" wrote in message ... Can I assume that you get the same error message when you insert rows manually? Do you have any objects on the sheet? (Press F5, select Special, click Objects and click OK). If you have some they will be selected. Press Del to delete them (you're not going to save this so go ahead). Still have the problem? This will not select cell comments, which is a leading cause of this problem. But then you don't have any so no matter. -- Jim "Tim Wallace" <twallace-ThisDoesNotBelong-AT-emailDOTcom wrote in message ... | I've posted this problem in some C# groups, but after a couple of days, I've | not gotten any useful responses. So, here goes again. | | I have a C# application from which I need to open an Excel (2003 | 11.6255.6360 SP1) sheet, insert some rows and then leave open for the user. | I have no hidden rows or columns. I have no comments. (these facts are so | I don't get pointed to unapplicable Knowledge Base articles). Whenever I | attempt to insert a row, I get a "cannot shift objects off sheet" error, | even though all of the columns are visible and within the print area and | there are 8 blank rows visible and in the print area. Can anyone lend a | hand in figuring this out? | | I'm really at wits end on this, as this is the only thing keeping me from | completing my project, and I've been at it for a number of days now. | | Tim | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAN'T SHIFT OBJECTS OFF SHEET. WHAT DOES THIS MEAN? | Excel Discussion (Misc queries) | |||
Cannot Shift Objects Off Sheet | Excel Discussion (Misc queries) | |||
Cannot shift objects off sheet? | Excel Discussion (Misc queries) | |||
Cannot Shift Objects off sheet | Excel Worksheet Functions | |||
Cannot shift objects off sheet | Excel Programming |