Graphic Data Marker in Excel 2007 Charts

[Mohan Kumar Karunakaran]

Hi,

I would like to add some pictures as data markers in my line chart using
excel VBA. I tried recording a macro to do this but the code is not generated
for the insert picture part. Does any one have an idea for this.

Regards,
Mohan

 

[Andy Pope]

Hi,

Create a line chart with 2 series. Then with the chart selected run this
routine.
Make sure you change the UserPicture filename to one that is on your system.

Sub MyMarkers()
' from file
With ActiveChart.SeriesCollection(1)
.Format.Fill.UserPicture "C:\Waterfall.jpg"
.MarkerSize = 20
End With

' from shape on worksheet
With ActiveChart.SeriesCollection(2)
ActiveSheet.Shapes("Picture 1").Copy
.Paste
End With
End Sub

Cheers
Andy

 

[Mohan Kumar Karunakaran]

Hi Andy,

Thanks for your help. This solved my problem but I have one more question. I
tried to insert an image from the below mentioned link (a pin).

http://members.aol.com/_ht_a/wiseowlsw/shadows.htm

So when I save this picture to my harddrive and used the module provided by
you the image is loaded as datamarkers but with a border around it.

Is there anyway to show only the icon without the borders. The file
extension is .gif

Thanks in advance,
Mohan

 

[Andy Pope]

This should remove the markers border.

With ActiveChart.SeriesCollection(1)
.Format.Fill.UserPicture "C:\temp\lmauve.gif"
.MarkerSize = 20
.MarkerForegroundColorIndex = xlColorIndexNone
End With

Cheers
Andy

 

[Mohan Kumar Karunakaran]

That solved my problem Andy.

Many Thanks

This should remove the markers border.

With ActiveChart.SeriesCollection(1)
.Format.Fill.UserPicture "C:\temp\lmauve.gif"
.MarkerSize = 20
.MarkerForegroundColorIndex = xlColorIndexNone
End With

Cheers
Andy

 

[karl]

Apologies for asking a question in a reply (every time I try to start a new
thread I get nothing but a blocked pop up!)
I want to replace the markers in 2000 excel scatterplot charts with numbers
so for a given object I can identify its attributes across a number of
scatterplots.ie all the ones correspond to the first object, all the two's to
the second etc.is this possible and if it is how (keep in mind I'm just a
biologist not a programmer). Could I do it as per the Andy Pope instructions?
is there an easier way?
Thank-you for your consideration

 

[Del Cotter]

I want to replace the markers in 2000 excel scatterplot charts with numbers
so for a given object I can identify its attributes across a number of
scatterplots.ie all the ones correspond to the first object, all the two's to
the second etc.is this possible and if it is how (keep in mind I'm just a
biologist not a programmer).

I just use Data Labels. You can use the normal numbers in ordinary
fonts, or find the fonts which give numbers in circles or bullets, and
use the characters from those fonts. Format their alignment to
Center/Center, and then format the data series to have no conventional
marker.

No VBA programming required, but it's easier if you download and install
one of the two common free data label add-ins:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com

 

[Jon Peltier]

You can simplify this by naming the series '1', '2', etc., and assigning the
default series name data labels.

- Jon

 

[Jon Peltier]

I forgot that's one of the genuine improvements in 2007. In 2003 or below
I use my method.

The expanded set of data label options was introduced in 2002.

- Jon

 

[Del Cotter]

The expanded set of data label options was introduced in 2002.

Now you can see I've never encountered anything later than 2000

 

[Jon Peltier]

Now you can see I've never encountered anything later than 2000

Well, that's what I thought I remembered, but you said "2003 or below".

As far as I recall, adding the data label options was about the only chart
enhancement in 2002, but it was a good one. There were also a bunch of
enhancements to pivot tables. These may have made it worthwhile to upgrade,
but I hardly used 2002. When I upgraded from 97 to 2000, I was happy enough
to ignore 2002. A week or two after I finally upgraded to 2002, 2003 became
available, and it was more appealing than 2002. 2003 introduced the powerful
and useful Lists, which have become even more powerful Tables in 2007. While
the lists alone were reason enough for me to upgrade, 2003 was also more
stable than 2002 (a trend since 97 that seems to have skipped 2007). 2002
was a forgotten version, not just for me, but apparently for most users.
2007 deserves to be another forgotten version; it has a lot of potentially
nice new features, but there are too many gremlins and inefficiencies for
serious use. I only use it in virtual machines when developing for clients
who have upgraded. For my important work, I stick to 2003.

- Jon