Excel 2010: Run-time error at .Chart.Paste line within With statement (works on 2003/2007)

Hi all,

My employer is planning to move to Windows 7 and Office 2010 as existing computers come up for replacement. I have a program (developed by someone else who has since left the organisation) which runs fine on our outgoing Windows XP / Excel 2003 platform but which is crashing at a couple of points during testing on my own Windows 7 / Excel 2010 laptop at home. It previously worked fine on a Windows 7 / Excel 2007 combination.

The first, more critical probem occurs in a section of code which takes charts held within worksheets, exports them as Bitmap files and then displays them within a UserForm. In W7/E2010 I get "Run-time error '1004': Application-defined or object-defined error" at the .Chart.Paste line (I've posted the whole procedure for context).

Please Login or Register  to view this content.

Any help would be very gratefully received!

Cheers,
John

Hi Andy,

Thanks for offering to look at this. The workbook was too big to post in its original state so I started trying to cut it down but also did some further testing and have found a solution.

I noticed that the positioning of the DataFTE sheet (from where tables and charts are exported for display in the form) had a bearing on which run-time error I got. This also explains why the isolated macro didn't crash in your testing.

If prior to running the sequence of macros the DataFTE sheet is left scrolled far enough to the right that column G is no longer visible (meaning there is enough space to the left of the visible screen for a chart to be placed) the process places a completely blank chart (a white box with no axes, legends etc. but of 'Chart' type) in cell A1 in the DataFTE sheet at the start of the With statement within the TableShow module and crashes with error 1004 at the .Chart.Paste line.

If I leave the DataFTE sheet scrolled far enough to the left that column G is wholly or partly visible the process gets past that module but instead crashes on the last line of a later ChartShow module with error 481 'invalid picture'.

Selecting the desired chart manually first and ensuring the screen is scrolled to the left solves both of these run-time errors and allows the macros to run. I've added the following code in order to automate this regardless of what happens prior to running the sequence:

Please Login or Register  to view this content.

In practical terms this one is 'Solved', though it's strange that this additional code is needed in 2010 but not in 2007 or 2003.

Cheers,
John

@ JohnMcCallum

Based on your last post it seems that you solved your problem on your own but you haven't properly marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.