Excel 2007 Chart-Objects don't work properly

[lapete]

Hi,

I hope someone is able to help me. I just switched from Excel 2003 to Excel
2007. In Version 2003 I made a kind of graphical analysis feature where I use
excel-charts embedded into a worksheet. Hereby I use chart-events to
influence my charts.

Now I've got some problems which I didn't have in Excel 2003.

1. I noticed that there are several properties within the chart object model
which I cannot use when I set up object variables. For example:

this code will not work (run-time error):

Dim xlChartObject As Excel.ChartObject
Set xlChartObject = ActiveSheet.ChartObjects("Diagramm 1")
xlChartObject.ProtectChartObject = True

But this code works:

Dim xlChartObject As Excel.ChartObject
Set xlChartObject = ActiveSheet.ChartObjects("Diagramm 1")
ActiveSheet.ChartObjects(xlChartObject.Index).ProtectChartObject = True


Why is this so? I noticed this behaviour with several properties.


2. The second problem I have concerns chart events. In Excel 2003 I locked
the worksheet and I still could catch chart events of embedded charts. But if
I lock the worksheet in Excel 2007 then I can no longer catch any chart
events.


Please help me with these points.
Best regards, Lars

 

[lapete]

Is there no one who is able to help me?
Lars

 

[Jon Peltier]

I've filed this away until I have time to validate the problems.

1. I have noticed that Excel 2007 VBA does a few things differently than
Classic Excel. Mostly it's little stuff, like you've posted. Little stuff
that still brings a routine to a crashing halt.

2. I haven't checked out events in a protected sheet. Is the chart also
protected? Can you select chart elements though the sheet is protected? I do
know that MouseUp doesn't work right in 2007, but you can get around most of
that by using MouseDown. I have yet to test much of my event code to see
what's affected. Most clients who have needed this functionality are savvy
enough to have waited to upgrade (or at least savvy enough to have asked me,
then heeded my advice).

- Jon

 

[lapete]

Thanks for giving some advice. I have got the impression that there are a
lot of functions which are not working properly in the object model of excel
2007. Another bug is the method "GetChartElement". It gives a wrong "Arg2" if
you click on the X-AxisTitle.

Concerning the chart events I made some experiments: when the worksheet is
unprotected the events work fine. When I lock the chart and then protect the
worksheet, chart events do not work anymore. If I do not lock the chart and
then protect the worksheet (without objects being editable) then events do
work except the titles and legends. If I do not lock the chart and protect
the worksheet with objects still being editable then all events work fine.

Now what I actually want to do is that all events work fine and that when
clicking the mouse button (I always used the mousedown event) the chart
should not be selected and highlighted. As well should no cell being selected
when clicking somewhere within the charts. I have made a sheet with a lot of
small charts where the users do changes only by erasing the chart events.
Then it is a little annoying when the charts a being highlighted by every
click of the mouse. As well there seems to be a difference between excel 2003
and 2007 concerning the mousepointer. In 2007 the mousepointer changes when
you move over a chart. Is there a possibility to change this?

Maybe you've got some further suggestions.
Lars