Excel Freezes - Something to do with a chart

[Andreww]

Hi - I have a highly customised xl report system which up till
yesterday was perfectly stable.

There are various option/radio buttons across the top to allow the
user to select volume/spend etc, drop downs to allow for different
filters etc.

The main part of the report is a grid which is populated using VB (it
populates another sheet with vlookup formulae and then the actual
report grid cells access the vlookup cells using offsets - needs to be
like this as there is a hidden columns issue).

Then there are 2 charts below the grid showing the colum and row data
which change when user clicks on a grid cell - all worked fine till
yesteday. I think the difference was that I changed the right charts
cell refs to be on another sheet.

When user clicks on one of the radio buttons the system freezes. If I
take the right hand chart out all seems fine.

When I come to put the chart back in the chartnumber of the next cell
seems to be somewhat greater than what is was previously. Am I correct
in thinking that every new chart gets the next number along. If so
it's as though a load of new charts have been created. Same has
occurred with various buttons and the radio buttons.

I am using XL 2003 on Windows XP prof. My employers don't want me to
download the office update for all sorts of reasons.

Any ideas?

Thanks

Andrew

 

[matt]

Hi - I have a highly customised xl report system which up till
yesterday was perfectly stable.

There are various option/radio buttons across the top to allow the
user to select volume/spend etc, drop downs to allow for different
filters etc.

The main part of the report is a grid which is populated using VB (it
populates another sheet with vlookup formulae and then the actual
report grid cells access the vlookup cells using offsets - needs to be
like this as there is a hidden columns issue).

Then there are 2 charts below the grid showing the colum and row data
which change when user clicks on a grid cell - all worked fine till
yesteday. I think the difference was that I changed the right charts
cell refs to be on another sheet.

When user clicks on one of the radio buttons the system freezes. If I
take the right hand chart out all seems fine.

When I come to put the chart back in the chartnumber of the next cell
seems to be somewhat greater than what is was previously. Am I correct
in thinking that every new chart gets the next number along. If so
it's as though a load of new charts have been created. Same has
occurred with various buttons and the radio buttons.

I am using XL 2003 on Windows XP prof. My employers don't want me to
download the office update for all sorts of reasons.

Any ideas?

Thanks

Andrew

Andrew,

If you search "ChartObject Object Model" in the VBE Help, you'll see
the hierarchy of the Chart Object. The diagram is "hairy" but still
informative. In general, when you embed charts on a worksheet they
take an index number. The first chart added is one, the second is
two, and so on.

The embedded chart path is as follows: Application/Workbook/Worksheet
of Chart (i.e. Chart is a chart sheet)/ChartObject/Chart. If you need
to find out information about the charts on the worksheet you can use
a For...Each loop. Something like the following:

sub getChartObject()
Dim chObj As ChartObject
For Each chObj In ActiveSheet.ChartObjects
Debug.Print chObj.Chart.Name
Next
End Sub

If you use the indexed number, then you'll the syntax would look
something like this:

ActiveSheet.ChartObjects(1).Chart

I'm not sure if you are deleting/adding charts as the user selects
various radio buttons, but I would look into simply changing the "Data
Source" of an existing embedded chart instead. Maybe you're already
doing this, but it's just an idea. I hope that this helps.

Matt

 

[Andreww]

Hi Matt - No I'm not recreating, the data in the charts just changes
as the data in the grid is changed.

Thanks Anyway!

Anyone have any other ideas?

The crash produces no error message, just seizes up. Event viewer
just says Excel has crashed and offers:

Hanging application EXCEL.EXE, version 11.0.6560.0, hang module
hungapp, version 0.0.0.0, hang address 0x00000000.

....and

Fault bucket 205435155.

Cheers

Andrew