• 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.

Find Legend Key position

S

sifar786

Member
Hi,


I am using VBA code to create an XYScatter chart using data. As we know that the Legend Key size is too small and cannot be resized and takes the max size of the Legend font, I want to replace each legend Key with a randomly colored circle (drawing toolbar) showing the X, Y values in the center. I am already replacing the data markers on the chart with randomly colored circles, with their centers showing the x,y values.


My question is, How do i find the Legend Key position and paste the appropriate circle containing the appropriate x,y values for that legend?
 
When you say "Legend Key size is too small and cannot be resized"

I am unsure wha you mean

If you select the legend box and then increase the font size, the Legend text changes size, You may have to manuall adjust the Legend box size with the small circular handles.


The Legend Icons are the same size as the Markers for your chart series.
 
Hi Hui,


Thanks for your reply.


please look at this chart.


http://goo.gl/photos/vw6GjDhLBR


the Legend texts on right and bottom are actually textboxes as this chart was created manually. Since i am unable to create the background blocks, i have just created an XY Scatter using VBA code, replaced each data series with randomly colored circles. Now these circles i also want to see as Legend Keys. But the Legend keys are small and so i want to find their position and again do a VBA copy-paste to replace the default ones with the randomly colored Circles.


Here's what i have achieved.


http://goo.gl/photos/mBfigZv0NR
 
Sifar

You can do most of what is required in Excel without vba


I have added a second chart behind your current chart to do the colored background

a Description of that can be found at http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html


I have mocked up a small example at http://rapidshare.com/files/441199373/Sifar.xlsx
 
Hi Hui,


Thanks a lot :) i will look at the file (especially the values you used to create the quadrant one).


I tried that example on peltiertech, but i saw that i cannot customize the X-axis width of the each quadrant. Some blocks have greater width than other blocks. Secondly, even if i create that it, how would i align their X,Y axis to match correctly?


Later on i have created a button on the sheet which exports this chart as JPEG file. but since i have 2 charts, it only exports the xyscatter (the pic which you saw) excluding the background block chart.


let me know, if i can post the file to you for your perusal.
 
Sifar


You can make columns of varying width see: http://peltiertech.com/WordPress/marimekko-charts/


I would use Alt and Drag the Cnr's of the Charts Plot Area so that they snap to underlying cells and hence are aligned.


Similarilly Print a Range of Cells to a File rather than the Chart, that way you'll get both charts in the picture.
 
Hi Hui,


I have seen the marimekko charts which are made using 100% Area charts, but wasnt able to get it properly. How do i make the background you made, using Marimekko charts? what would be the plotting points?


[blockquote]I would use Alt and Drag the Cnr's of the Charts Plot Area so that they snap to underlying cells and hence are aligned.[/blockquote] - I did not understand this. How do i select the underlying chart to snap both their columns?


"Similarilly Print a Range of Cells to a File rather than the Chart, that way you'll get both charts in the picture." - Sorry, but which file? which range of cells? can i show you my file?
 
Just FYI,


on Y-Axis (Salience), the height of each block is like this:

1st block height from 0-12

2nd block height from 12-25

3rd block height from 25-87

4th block height from 87-100


on X-Axis (Attraction), the width of each block is like this:

1st block height from 0-20

2nd block height from 20-35

3rd block height from 35-83

4th block height from 83-100
 
Well, i understood the last part regarding printing. But the chart location is a Chart Sheet & not a worksheet. Secondly when i change the location to a sheet and select the cells and print the whole worksheet to a file, it prints to a prn file & does not save it as a JPEG image.


Cant we group both the charts, aligned along their axes, and export them as JPEG?
 
Hi Hui,


I am still not able to align the charts. Do you have any solutions to my questions?


Any ideas how to accomplish this as this is an important assignment for the weekend.


Thanks in advance.
 
Sifar


Last question first

This may end up sounding like a tutorial but bear with me


An Excel chart has 2 main areas, The Plot area and Chart area.

The Chart Area is the large rectangle that encompasses everything

The Plot area is where the data is plotted

These 2 areas can be resized independently with the Plot Area always being smaller than the chart area.

Place your chart on a worksheet

Select the Chart Area

Now Hold the Alt Key down and Drag the Corners/Edges.

As you drag you will notice that the chart snaps to cell edges

Get the chart area about right but bigger than you need it

When finished release Alt

When you resize any of the cells, rows or Columns that are under the chart the chart will now resize with the changes.


Now do the same with the Plot Area within the Chart Area


Do that first with the background Plot (The Quadrant Areas you want)


Now repeat the same with the front chart and place it directly over the top of the first.


The front chart will need to have both The Plot area and Chart area fill colors set to No Color, so that the background chart is now visible through the front chart


This can be fiddly but is worth the effort.


Once in place you can use the Send Forward/Back Buttons on the Chart Format tab to get access to the back chart and vise-versa
 
Second last question

Saving a Range as a file


Select the Range you want to save which will include your charts or parts of them

Now on the Home Tab select the small drop down arrow next to the Copy Button

Select Copy as Picture

Select As Shown on Screen and Picture options


You can now go to Paint and Paste the picture and save as any format you wish


There are a few utilities around which can do that with a few clicks and save as a JPG, GIF or PNG file format J-walks PUP utilities is the one I use.
 
Thanks Hui. :)

i am using Excel 2003. Also in ref to my earlier question, is it possible to create the background chart with the given X, Y block height and width?

on Y-Axis (Salience), the height of each block is like this:

1st block height from 0-12

2nd block height from 12-25

3rd block height from 25-87

4th block height from 87-100


on X-Axis (Attraction), the width of each block is like this:

1st block width from 0-20

2nd block width from 20-35

3rd block width from 35-83

4th block width from 83-100


Is it possible to create this background using Marimekko chart and if so how?


Thanks.
 
Do you want the Good News or the Bad News ?


Good News: I have completed the Marimeko with your data using the above link

It is attached at http://rapidshare.com/files/441593842/marimeko.xlsx

The instructions had to be followed to the letter


Bad News: Although the chart is now correct, the individual cells cannot be individually colored which you need to do.


Options: You have 2 options maybe someone else can assist for more?


1. Try and make a much more complex chart with individual series for each cell. That way each will be allowed to have its own color

2. Get your 2 charts to the right size. Don't snap to cells

Now adjust the underlying rows and columns so that each column and row aligns with the edges of the marimeko chart's individual cells.

You can now color the cells individually and move the marimko chart out of the way.

3. Insert Regular Shapes which overlay the marimeko chart and are the colored as appropriate. You can group them and move to the back


I don't have time to assist any further than this.


Let us know if you find a better solution.
 
Thanks Hui.


I have used manually created the background as a picture and superimposed the plot area of the chart onto it. aligned the points to the plot area by using dummy points for the 4 corners, like:

0,0

0,100

100,0

100,100


Then used "Bring to front."


Though i still have to tackle a couple of things for the chart using VBA, though that would be a separate post.


Thanks once again :)
 
You must log in or register to reply here.
Back
Top