ActiveX control with chart

[Erich Neuwirth]

I have an ActiveX control producing some special charts
which cannot be done in Excel itself.
I would like to embed this control into Excel sheets,
and they should behave similar to Excel charts
(e.g. resize when the lower right corner is grabbed and moved).
The chart object in Excel does have an OLEObjects function method,
so it seems to be able to report embedded OLEObjects,
but it does not have an Add method for such objects.
Can I embed my ActiveX object in a chart,
or at least resizably in a worksheet?
OLEObject's don't have a resize event, so
"plain" embedding does not seem to work.
Somebody on another group mentioned that it possibly can be
done with Forms from the Forms toolbar.
Is this a viable solution. Are there example anywhere?

 

[Jon Peltier]

Hi Erich -

Last year I installed a third party ActiveX chart control, thinking I'd
use it for some funky new charts. Five minutes ago I used it for the
first time, to get a handle on your question. I insert the chart using
Insert menu > Object, and selecting it from the list.

I could activate the control's dialog with the right button, but
otherwise I couldn't select or activate the control. The best I could do
in vba was to change its position (activesheet.shapes(1).top and .left)
and its size (.width and .height) but not its aspect ratio.

Then I switched to design mode (click the quaint T-square and triangle
button on Excel's Visual Basic command bar). This enabled me to select
the objects, move and size it with the mouse, and change the aspect ratio.

Interestingly, as soon as I ran something from the VB Editor, Excel
exited design mode, and VB was unable to do very much. Excel created a
reference to the control, but for the life of me, I could see nothing
that talked of a container for the control. I suspect the container is
just a nondescript Shape.

I even turned on the macro recorder while I played for a while. The kind
of code it generated looked like this:

ActiveSheet.Shapes("Chart2D2").Select
Selection.ShapeRange.IncrementLeft 40.5
Selection.ShapeRange.ScaleHeight _
0.75, msoFalse, msoScaleFromBottomRight
Selection.Verb Verb:=xlOpen
Selection.Verb Verb:=xlPrimary

Seems like all Excel can see is the Shape that encloses the control.

I hope this rambling gives you some insight.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Erich Neuwirth]

Hi Jon,
your message essentially confirms most of the things I have tried.
It is next to impossible to embed custom chart controls in a way
they behave similar to Excel charts.
The strange thing I found is that chart objects have a (function) method
OLEObjects, so this creates the impression that one can ask a chart
object about embedded objects, but I have not found a way which would
allow to set an embedded object.

This is really a pity because extending the range of available graphics
would be quite nice.

 

[Jon Peltier]

Erich -

I just checked the object browser about Chart.OLEObjects, which I'd
never happened across before. Interesting. There's no example of
OLEObjects with a chart, only with a worksheet. This example indicates
that it deals with OLE objects embedded in the sheet. And that's cool,
you can embed an OLE object into a worksheet. But you can't embed one
into a chart sheet or embedded chart; copy and paste one and you get a
picture object. I can't determine what the OLEObjects really means,
unless it's just there to keep the Sheet object from crashing (or can we
hope about a future enhancement??). So either I'm misinterpreting what
the chart's OLEObjects means, or it means nothing.

A MS Graph chart (the kind that you get in PowerPoint or Word) exists in
an OLE object, and you can program it through the OLE object. The reason
you can't program (or even manually adjust) the ActiveX chart object
within Excel is that it wasn't programmed to allow you to do so. Some
ActiveX graphical tools are designed to let you manipulate them in
Excel. I tried out a Heat Map ActiveX thing last year which integrated
nicely with Excel, and I got a sample CD for another ActiveX chart which
supposedly works with Excel (all I read was the readme file).

If you find anything useful, though. please let us know.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Tanihera]

Hi Jon

Did you ever resolve this issue you discussed with Erich Neuwirth?
have an almost identical problem: I've OLEembedded various active
picture controls on worksheets, only to find I can't load images int
them, see images in them, or see their properties and methods.

I can activate an object using MyObject.Active, but this seems t
change nothing. I can select an OLEObject with MyObject.Select, bu
then using With Selection, the tipswindow only shows the shee
properties, not the OLEobject properties.

As you say, it seems like all Excel can see is the Shape that enclose
the control. I'd be extremely grateful for any help from anyone.

Hi Erich -

Last year I installed a third party ActiveX chart control, thinkin
I'd
use it for some funky new charts. Five minutes ago I used it for the
first time, to get a handle on your question. I insert the char
using
Insert menu > Object, and selecting it from the list.

I could activate the control's dialog with the right button, but
otherwise I couldn't select or activate the control. The best I coul
do
in vba was to change its position (activesheet.shapes(1).top an
.left)
and its size (.width and .height) but not its aspect ratio.

Then I switched to design mode (click the quaint T-square an
triangle
button on Excel's Visual Basic command bar). This enabled me t
select
the objects, move and size it with the mouse, and change the aspec
ratio.

Interestingly, as soon as I ran something from the VB Editor, Excel
exited design mode, and VB was unable to do very much. Excel create
a
reference to the control, but for the life of me, I could se
nothing
that talked of a container for the control. I suspect the containe
is
just a nondescript Shape.

Seems like all Excel can see is the Shape that encloses the control.

- Jo

-
Taniher

 

[Jon Peltier]

A blast from the past. I suspect I gave up on the ActiveX charting control,
since I don't remember anything further.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Hi Jon

Did you ever resolve this issue you discussed with Erich Neuwirth? I
have an almost identical problem: I've OLEembedded various activex
picture controls on worksheets, only to find I can't load images into
them, see images in them, or see their properties and methods.

I can activate an object using MyObject.Active, but this seems to
change nothing. I can select an OLEObject with MyObject.Select, but
then using With Selection, the tipswindow only shows the sheet
properties, not the OLEobject properties.

As you say, it seems like all Excel can see is the Shape that encloses
the control. I'd be extremely grateful for any help from anyone.

Hi Erich -

Last year I installed a third party ActiveX chart control, thinking
I'd
use it for some funky new charts. Five minutes ago I used it for the
first time, to get a handle on your question. I insert the chart
using
Insert menu > Object, and selecting it from the list.

I could activate the control's dialog with the right button, but
otherwise I couldn't select or activate the control. The best I could
do
in vba was to change its position (activesheet.shapes(1).top and
.left)
and its size (.width and .height) but not its aspect ratio.

Then I switched to design mode (click the quaint T-square and
triangle
button on Excel's Visual Basic command bar). This enabled me to
select
the objects, move and size it with the mouse, and change the aspect
ratio.

Interestingly, as soon as I ran something from the VB Editor, Excel
exited design mode, and VB was unable to do very much. Excel created
a
reference to the control, but for the life of me, I could see
nothing
that talked of a container for the control. I suspect the container
is
just a nondescript Shape.

Seems like all Excel can see is the Shape that encloses the control.

- Jon