Pictures (bitmaps) as Point Markers in XY Scatter Plot?

[Jim S]

Hi,

I've figured out how to load custom bitmaps into Shape
objects and use them as Marker Points on my scatter plot,
but I'm not sure I'm doing it in the best/correct way,
especially for "portability" of my workbook.

By "portabiliy", I mean, I want to send the Workbook to
someone but don't want to have to send all the bitmap
files along with it. Is there a way to make these
shapes "part of" the workbook so that they're always
available?

Here's what I'm currently using to load the various
bitmaps:

dim myShape as Shape
Set myShape = ActiveChart.Shapes.AddPicture( _
Filename:=<<path to file>>, _
LinkToFile:=msoTrue, _
SaveWithDocument:=msoFalse, _
Left:=<<left>>, Top:=<<top>>, _
width:=<<width>>, height:= <<height>>)

And then to apply a specific Shape to a specific Point, I
use:

Dim myChart as Chart
myShape.Copy
myChart.SeriesCollection(1).Points(1).Select
Selection.Paste
(Is there a better way to apply the Marker??)

Now, the Shapes collection appears to be a property of a
particular chart. What's the best way to have all of
these bitmap/shape objects available for use without
having to load them from a file? I would guess that I
should probably do something like this:

1. Change LinkToFile to be False and SaveWithDocument to
be True.
2. Create a "dummy" (read: hidden) worksheet or
chartsheet with the bitmaps/Shapes loaded in them, and
save the workbook.
3. Whenever I create a new chart in VBA, just go to
that "dummy" (hidden) workbook to copy the existing Shapes
and apply them to Points in the new chart.

Then, the application of a Shape to a point would look
something like this:

Charts("my dummy sheet").Shapes("some name").Copy
myNewChart.SeriesCollection(1).Points(1).Select
Selection.Paste

Is that how I should do this? Or am I making this WAY too
complicated (likely).

Thanks in advance for any help.

JS

 

[Jon Peltier]

Jim -

You got it right. Use insert picture to load the bitmaps into the hidden
worksheet, give them all names, then copy and paste. You don't need to
select the point first, though, just use this:

Charts("my dummy sheet").Shapes("some name").Copy
myNewChart.SeriesCollection(1).Points(1).Paste

And you can use Sheets("my dummy sheet").Pictures(Name or Number).Copy

I always put my pictures in a dummy worksheet, and in the cell next to
the picture, I put its name, so I can see it quickly.

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