Rectangle graph

Hi,

I would like to develop an xy-graph consisting of coloured rectangles close to each other.

The basic data are:

Please Login or Register  to view this content.

The x-values represent per cent and the cumulative sum equals 100. The x_cumulative values represent the x-axis in the graph.
y and x_cumulative values make up the rectangles, in this case 5.
The rectangles are labelled inside the box and would have different colours.
The graph should have a secondary y-axis with the same appearance as the primary y-axis.

I started the programming but came only a small bit.

Please Login or Register  to view this content.

Kind regards,

Jan

Hi Andy,

It is almost what I am looking for.

However, I want to have an x-axis going from 0 to 100.
Besides, I would appreciate a secondary y-axis with the same appearance as the primary one.

The row with the values 20%, 15%, 12%, 30% etc. from the shown graph can be kept, because there I would like to show the the real x-values, not percentage, from column 2 , i.e. 10, 35, 20, 15, 20 in my test table.

Kind regards,

Jan

Hi Andy,

Do you have the VBA-code for the graph linked to your indicated address
http://www.andypope.info/charts/colwidth.htm ?

If so the changes might not be so big to get what I am looking for.

Kind regards,

Jan

Hi Andy,

It is pity that you don't have the code. I have drawn the graph manually, but if you have many graphs of that type it would make it easier if you had a macro.

Kind regards,

Jan

Hi,

I have the following table where x-y-values are the coordinates for the corners of in this case 4 rectangles. The x-axis goes from 0 to 100.
I am looking for the VBA macro to create this. The rectangles should be in different colors and labelled A, B, C and D.

Please Login or Register  to view this content.

Can someone help me?

Kind regards,

Jan

Hi again,

The data of the two last lines in the example were somewhat displaced. They should be in the last column.

Kind regards,

Jan

Hi Andy,

I tried to use the macro you wrote but it did not function for me. I suppose the reason is that I do not know exactly how the new basic table should look like. Could you please rewrite my table with your changes so it will function. Your statement Range ("A2:A8") looks curious to me. Is it the values for a single rectangle including the label?

I have not been able to really test the macro but when I read it I cannot see anything about colors of the rectangles. Will the rectangles have different colors?

Kind regards,

Jan

Hi Andy,

I have copied your Excel-file with the basic data. When I run the macro I get the message "Application defined or object defined error". The program indicates that the statement "Do while rngDataX.Cells(1, 1).Offset(0, -1) <> " is not correct. What do you say?

I do not understand the statement Set rngDataX = Range("A2:A8"). Are we then not outside the data of the Excel sheet? Column "A" is a data empty column if I have understood you right.

Kind regards,

Jan

Hi again Andy,

I think I have found the error. I changed Range("A2:A8") to Range("C2:C8") and the program now runs properly.

But I have still a question. How to get the rectangles colored, not only the borders?

Kind regards,

Jan

Hi Andy,

Thank you for all your help. Now, the macro functions properly except that I had hoped to get rectangles filled with different colors. Not only borders in different colors.

How can the x-y-diagram be changed to column charts to make this possible?

Kind regards,

Jan

Hi Andy,

I have tried to edit the y-axis and "Major gridlines" somewhat but with little success.

To improve the appearance of the rectangles I have also tried to add a thin, black border around them but I am uncertain of the VBA code.

Kind regards,

Jan

Hi Andy,

I have tried to develop the graph further but with little success.

1. Major gridlines should be dotted or dashed but have disappeared completely.

2. A dotted red line representing the average y-values with the label "Average" in red should be added. (Red data in the data sheet).

3. A new set of labels (AAAA, BBBB, CCCC, DDDD) have been added but they do not seem to have been included in the graph. Is the location of the graph influencing the data of the cells? The graph covers some of the data.


Kind regards,

Jan

Hi Andy,

I tried to extend the labels with another series (AAAA, BBBB, CCCC, and DDDD), but then the macro does not seem to function at all. Have you any explanation to this?

Besides, the color of the dotted line representing "average" is still blue, even if the code indicates that the line should be red. Why?

Kind regards,

Jan

Hi Andy,

I have tested the above macro under more realistic conditions. The result is, however, not encouraging.

If you open the attached file "Rectangle_graph -Sheet1" and run the macro "VariWidthColumnsSmall" it functions properly.

If you instead open "Rectangle_graph -Sheet2" and run the macro "VariWidthColumnsLarg" it does not function at all. Do you have any explanation to that? The macros should be the same except that they are adjusted for the number of cells included.

Kind regards,

Jan

Hi Andy,

I tried to add another series of data to the graph (AB1:AI1) but then the program indicated an Error. Do you have any explanation to that?

Kind regards,

Jan

Hi Andy,

I have got very valuable help from you with the enclosed macro.

I would like to go somewhat further and test the labels: "AA", "BB", "CC" and "DD".

Are some of the labels numbers and if so, are some negative, i.e. less than 0. Then negative numbers shall be in red color instead of black in the graph.


I also wonder if it is possible to choose different color palettes for the graphs? If so, I would like to have somewhat brighter colors than at present.

Kind regards,

Jan