• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create XY Scatter chart with VBA, align plotarea to 4 corners of Background pic

S

sifar786

Member
Hi,


1] I am trying to create an XY Scatter chart and align the 4 corners of its plot area to the 4 corners of a background picture so that the chart axis points meet the background pics corners correctly. The chart X & Y axis should not be visible.


2] I want to delete & create the chart(NOT THE BACKGROUND PIC), everytime i click the "CreateChart" Button.


3] The source data table for the chart whould be in blocks (POT) like this:

BLOCKQUOTE

Attr Sal POT# PAN# POT-PAN Pair

78 97 1 1 1.1

67 55 1 2 1.2

43 65 1 3 1.3

55 12 1 4 1.4

33 34 2 1 2.1

75 65 2 2 2.2

26 34 2 3 2.3

37 54 2 4 2.4

32 78 2 5 2.5

87 66 3 1 3.1

44 56 3 2 3.2

88 12 3 3 3.3

89 14 3 4 3.4

12 0 3 5 3.5

0 0 4 1 4.1

0 100 4 2 4.2

100 0 4 3 4.3

100 100 5 1 5.1

15 10 5 2 5.2

56 23 5 3 5.3

98 89 5 4 5.4


/BLOCKQUOTE


the data points for X-Axis: Attr Column

the data points for Y-Axis: Sal Column


4] POT column shows the groupings. Altogether 5 groupings. so there would be 5 Series names. Hence each group of data series & data labels would have similar color.


5] The data series name would be from column POT-PAN Pair.
 
Sifar

Can you make up a small example worksheet and post it somewhere with what your after
 
Hi Hui,


I have achieved 1] & 2] but stuck on the 3rd part.

How do i loop through the series groups and color their datalabels and series-names using a similar color for each group?


Also, i am not able to correctly align the chart plot area with the edges of the picture (excluding the 2 vertical and horizontal bands). I have tried increasing the chart area so that the X, Y AxisTitles are visible.


PFA the file for your perusal with the code.


http://rapidshare.com/files/441856594/TestIt.xls
 
Sifar


Easiest way to color would be to have an Internal Array of colors in the code

Then as you set and define MySeries lookup that value of the Pot and then use a matching color from your color array


eg:

Dim mycolors(10) as double

mycolor(1)= 123456

.

.

mycolor(10)=654321


replace

myChtObj.Chart.SeriesCollection(iRow).MarkerForegroundColor = RGB(Int(((255 - 0 + 1) * rnd) + 0), Int(((255 - 0 + 1) * rnd) + 0), Int(((255 - 0 + 1) * rnd) + 0))


with

myChtObj.Chart.SeriesCollection(iRow).MarkerForegroundColor = mycolor(rngChtData.Cells(iRow, 1).Offset(0, 4))


Same for the data labels
 
Sifar

RE: Size of the chart


Try the following which is your code towards the bottom of the cmd_CreateChart_Click module

Remember that you will need to have enough room above and to the left of the Picture to put the Chart

I made Row 1 & Col 1 both 130 Pixels

[pre]
Code: 
' align chart plot area with picture dimensions
Const os = 50

With myChtObj.Chart
With .ChartArea
.Top = Top1 - os
.Left = Left1 - os
.Width = Width1 + (2 * os)
.Height = Height1 + (2 * os)
End With
With .PlotArea
.Top = Top1
.Left = Left1
.Width = Width1
.Height = Height1
End With

End With
[/pre]

I also commented out


'myChtObj.Chart.Axes(xlCategory).AxisTitle.Top = pic.Top + pic.Height + 25

'myChtObj.Chart.Axes(xlValue).AxisTitle.Left = pic.Left - 135


Because the chart seems to adjust itself anytijme anything is changed


As a final comment I refer you to:

http://www.dailydoseofexcel.com/archives/2010/09/14/placing-shapes-with-vba/

Which sort of goes back to where I suggested that you make the background out of cells

You can use code to change the width/height to suit the charts dimensions and then snap the chart to the cells as described in the above link
 
Hi Hui,


Thanks for the info.


myChtObj.Chart.SeriesCollection(iRow).MarkerForegroundColor = mycolor(rngChtData.Cells(iRow, 1).Offset(0, 4))


My problem is, how do i determine how many offsets to make, as next time, each pot size may change. i need to group & color similar values in the pot column.

e.g.


POT#

1

1

1

1

1

2

2

3

3

3

3


I am pulling this POT data from another sheet. I need to compare each cell in POT column with the corresponding cell value below it and see if it matches it.
 
Sifar

You don't need to

Setup an array with say 20 colors (or more than your ever likely to need)

Each entry will be mycolor(1), mycolor(2)..mycolor(20)


The line

myChtObj.Chart.SeriesCollection(iRow).MarkerForegroundColor = mycolor(rngChtData.Cells(iRow, 1).Offset(0, 4))

will use the color number from the mycolor array based on the value of Pot# which is retrived using the rngChtData.Cells(iRow, 1).Offset(0, 4) part of the formula.


Ie: Pot#1 above will use mycolor(1)

Pot#2 will use mycolor(2) etc
 
Hui,


a pot can have multiple pans in it and their number can vary. similarly, the number of pots can vary. i hope you understanding what i am trying to convey.

so i cannot use :

rngChtData.Cells(iRow, 1).Offset(0, 4)


it should be some variable which i would determine at the onset itself

rngChtData.Cells(iRow, 1).Offset(0, somevar)


OR


it should be a similarity comparison with the pot# below. so (the dataseries n markers of)all pots=1 will be say, Red, all pots=2 will be green, all pots=3 will be blue etc.


http://rapidshare.com/files/441919049/TestIt.xls
 
i used you code...


' align chart plotarea with picture dimensions

Const os = 50


With myChtObj.Chart

With .ChartArea

.Top = Top1 - os

.Left = Left1 - os

.Width = Width1 + (2 * os)

.Height = Height1 + (2 * os)

End With

With .PlotArea

.Top = Top1

.Left = Left1

.Width = Width1

.Height = Height1

End With

End With


getting an error...


"Runtime error 1004"

"Unable to set the Top property of the ChartArea Class"


I know this has something to do with some object getting activated, but i am unable to trace which object..
 
Try putting a Dim os as integer at the top of the module

and change Const os=50 to os=50
 
I have tried your suggestion but found that its happening only with the .ChartArea part starting with viz.


.Top = Top1 - os

...

http://rapidshare.com/files/441958213/TestIt.xls


[see attached]
 
I'm not getting any errors in Excel 2007 or 2010?

What are you using ?


I would still go to a cell based approach rather than try to connect to the Pic
 
I am still not able to align the chart properly. Not even to cells using Dick's example.

There must be something which is triggering that error for mychtobj.chartArea. Is it something to do with focus of the chart? e.g. Chart send to back or bring to forward?
 
You must log in or register to reply here.
Back
Top