Automatically re-sizing chart to fit Data and Vertical Catalog Axis

[FranktheBank]

I finally got the Dynamic Ranges working and it's almost all GOOD, except two last problems:

1) My Vertical Category Axis is feed with two Columns: Column 'A' giving the Position number and Column 'B' giving the name of the individual in the position. On the Chart the Position number is entered vertically while the Name is reported Horizontally. How Can I get both Horizontal? I can't stretch the Vertical Category Axis box and I can't find if/where I can change the Box size.

2) How do I set the height of the chart to adjust with the data so if it's only providing information on five positions as per the picture or 123 positions the graph is legible

Thanks as always

Axis_Problem.png

[FDibbins]

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[FranktheBank]

Please see attached
Demo_Human_Resources_Salary_Burn_Template.xlsm

[MrShorty]

If you uncheck the "multi-level category axis" box (format category axis dialog), it will combine the two into a single, horizontal text string. Is that what you are looking for?

[MrShorty]

As for 2), is there a specific part you are having trouble with -- the "automatically" part or the "change height" part.

For the "change height" part, it seems like it should be as simple as choosing a suitable value for the .height property of the chartobject object: https://msdn.microsoft.com/en-us/lib.../ff193576.aspx

For the "automatically" part, I would expect it to be something associated with a change or calculate event.

[FranktheBank]

Thanks for your reply MrShort.

Where should I be finding the "multi-level category axis" box (format category axis dialog), in excel 2013? I have the format Axis Box open on the Right hand side, but I'm not seeing anything??

As far as the height issue, I don't care how thick the lines are exacly, I'm just trying to figure out a way that I can use the Pivot table to select different filters which will in turn produce a Chart that is legible every time. I added a demo about 4 posts up if you want to take a look.

Thanks for all your time.

[MrShorty]

Unfortunately, 2007 is as high as my knowledge goes. You might pull up the help files in your Excel installation to see if they can help you find this checkbox. I have put out a request to someone with 2013 to look at this and comment.

[MrShorty]

As far as the height issue, I don't care how thick the lines are exacly, I'm just trying to figure out a way that I can use the Pivot table to select different filters which will in turn produce a Chart that is legible every time. I added a demo about 4 posts up if you want to take a look.

Perhaps I am not understanding what you are asking here, and I did see your demo. In your demo, in the immediate window if the VBE, I entered activesheet.chartobjects(1).height=1000 (with sheet5 active), and the chart got bigger. So, it seems to me that the chartobject.height property can be used to vary the vertical size of the chart. From there, I would expect a trial and error process to figure out what value to put into the height property for a given number of categories.

[Andy Pope]

You can only control the alignment of the inner most labels.

Multi-level category labels option is within the Labels section of the Format axis task pane

[Keebellah]

What happens if you select the vertical axis?
I clicked it, selected the names and set the alignment to vertical
I attached your workbook. I got an error because I'm missing the calendar component but that's beside the pint.

[Keebellah]

And have you tested? You could at least have the decency to answer or say thank you if it works.

[FranktheBank]

Sorry I did not read the last three replies Friday.

I got the Vertical Axis problem solved by creating a third Dynamic Range which Concatenated (is that the proper term??) the two Dynamic Ranges. This idea came to me on the drive home.

Please Login or Register  to view this content.

I still have not figured out if/how to get the size of the chart to adjust automatically with varying amounts of rows??

[MrShorty]

I still have not figured out if/how to get the size of the chart to adjust automatically with varying amounts of rows??

What part of this are you having trouble with?

1) Counting the number of categories? I would expect a COUNT() or COUNTA() or COUNTIF() function should do this
2) Determining the desired height that you want to use with that number of categories? This is probably the hardest or most tedious part. I would expect that you will need some "trial and error":
2a) select pivot criteria that yield a certain number of categories.
2b) manually adjust height to the desired height for that number of categories
2c) Use the chartobject.height property to read the height and store it somewhere.
2d) Go back to 2a and select a different number of categories and repeat 2a to 2c.
2e) Analyze the heights vs number of categories until you can see a strategy for determining a suitable height for any number of categories.
3) Applying the desired height value to the chartobject.height property.

Where do you get stuck?

[FranktheBank]

The issue is if a person runs the Pivot Table with no Filters, it can contain 125+ rows, which would be an extremely large graph and the User would have to stretch it down to get all the information. Leaving the risk that the User may not stretch it down enough. If the user runs the Pivot Table with a Filter it could have a little as three rows which would be quite manageable.

What I am trying to accomplish is to have the size of the graph (the height) automatically grow/shrink pending on the amount of rows in the Pivot Table