Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2003 - Dynamic Chart Height

Posted on 2011-10-19
16
Medium Priority
?
470 Views
Last Modified: 2012-05-12
I have a horizontal bar chart Chart(549) on a sheet that includes data from a dynamic set of rows.
Let NumRows = UBound(UniqueFoRs, 1)

I wish the chart top Bar of the chart to remain in a fixed and constant position on the sheet.
I wish the Height of Chart(549) to increase/decrease according to NumRows.

The equation to change Chart(549).Height cannot be a simple ratio because that fails to take account of the top section of the Chart.  In other words the below algorithm cannot work

    ActiveSheet.Shapes("Chart 579").ScaleHeight 1.20

because this will move the position of the first bar in the chart downwards (because the Height is being scaled upwards) - it is the first bar in the chart that must remain in a fixed position.

Are there any other ways to manipulate the height of a chart like this?

I have coded Zoom = 100% during the running of the Macro, so you can count on that occurring.

A picture is attached so you can see the alignment between chart and rows of data – the number of rows will change.

chart-alignment.JPG
0
Comment
Question by:TigerMan
  • 7
  • 4
  • 4
  • +1
16 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37000216
You might like to include a working excel file for someone to have a go at it.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37000381
Do you have to use VBA?  I know that some chats will scale automatically if set to.

Another option is that you can use VBA to move the chart after your done scaling it.  So it always ends up where you want it.

If you post the workbook I'm sure a few of us will take a crack at it.
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 37001491
Have you tried using the Camera function in Excel?

If you haven't got the button on a toolbar, customise a Toolbar and select the Tools option in the left hand pane and scroll down in the right hand pane, strangely enough the icon is a camera.

You can select an area to be replicated as a picture. Once you ahve the picture when you select it the formula bar shows the range selected. This can be a named range so you could possibly expand this to be a dynamic named range which is "beneath" (cells covered/masked by as opposed to lower rows) the chart but big enough to show it all. The camera "picture" will then stretch downwards as required.

Thanks
Rob H
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 5

Author Comment

by:TigerMan
ID: 37003167
Hi Rob,
Some answers to your ideas ...
The working example is 2 large files and it is proprietary data.  So I cannot put a working example up.  However, creating one is a matter of
a) throw some data in a sheet
b) build a chart and locate side by side as in my example above
c) add some rows and then get the chart to resize according to specs

"Another option is that you can use VBA to move the chart after your done scaling it.  So it always ends up where you want it."
This is what i am asking in the question :)
Only difference is I am not scaling the chart.  Look at the picture and you see the chart is beside corresponding rows of data.  When I run my macro the number of rows will shrink or grow.  the chart is already set up so it uses a changing range of data but its size remains constant.  I need that size to grow/shrink with the rows of data and to say in line with the data.

Camera sounds messy and not VBA so probably not an option (also is this in 2003?)

Cool?
0
 
LVL 5

Author Comment

by:TigerMan
ID: 37003698
Rob, this is the type of thing I am looking for, but it is not 'perfect'.  the Chart Series are dynamic so new rows are plotted.

The problem is that I do not know how to control chart elements ... so the last bar tend to creep further and further below the last row of data the more rows I put in.

Has anyone done this stuff before and can advise?
Sub myMacro()
Dim RowCount, Heading, PlotTop

RowCount = Range("B1").Value
Heading = 60
PlotTop = 50

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveSheet.Shapes("Chart 1").Height = Heading + RowCount * 13

ActiveChart.PlotArea.Select
Selection.Top = PlotTop

ActiveChart.ChartArea.Select
Selection.Font.Size = 8

Range("B1").Select
    
End Sub

Open in new window

0
 
LVL 5

Author Comment

by:TigerMan
ID: 37004652
Most strange indeed ... doesn't anyone do any vba manipulation of charts?
The problem with the above approach is that the entire chart is dynamic and therefore changes in .Size makes everything autoscale in size - so the equation needed to control for these was too time-consuming for this exercise.
To reduce the overall change in dynamic components on the chart I removed the Title and stretched the chartarea to its maximum.  The code attached does quite well for between 5 and 20 rows of data ... outside that, the creep effect is more visible.
But it works and unless there is other more useful assistance, this would have to be an answer.
 dyn-cht-0912 dyn-cht-1103
Sub ResizeChart(UniqueFoRs)
Dim RowCount, ChtSize

RowCount = UBound(UniqueFoRs, 1)
 
ChtSize = (RowCount * 13)

ActiveSheet.ChartObjects("Chart 579").Activate
ActiveSheet.Shapes("Chart 579").Height = ChtSize

ActiveChart.ChartArea.Select
Selection.Font.Size = 7
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels.Font
    .FontStyle = "Bold"
End With

Range("J20").Select
    
End Sub

Open in new window

0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 37005008
There are options within the chart to switch off auto scale so when you stretch it the font stays the same.

Thanks
RH
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37005014
>> Most strange indeed ... doesn't anyone do any vba manipulation of charts?

I think that the bottleneck is an excel file. I myself am too lazy to setup an excel file and then attempt a solution. Had there been a file I would have had a go at it. Believe me. Throw in an excel file and see the difference in performance.
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 37005266
I recall seeing a solution on here that produced effectively a Gantt chart using conditional formatting to fill the cells in a row alongside the data label.

Doing something similar to this would ensure that the graphical appearance would be OK because the "graph" would actually be in the same row as your data.

However, I think the solution was in 2007 so may not be suitable but worth a search.

The camera tool is available in 2003. I have used it where I want to transfer elements of an Excel sheet as pictures to Powerpoint.

As mentioned by ssaqibh, a sample file would make things a lot simpler.

Thanks
Rob H
0
 
LVL 5

Accepted Solution

by:
TigerMan earned 0 total points
ID: 37006274
hi robhenson
i have used the conditional formatting in a different context, but it won't cut the rubber for this one - needs to be a chart and i have to copy paste-to-jpeg and save the picture (more than once) for different scenarios
the sample file is 5mb of proprietary data - the pictures above tell the whole story and really nothing else is needed ...
in the end i have played with the maths under the chart and found a formula that is 99.9% accurate for any number of records - i think the only limitation is that created by extremely small differences in the resolution of the numbers and how excel treats them - so the below is the answer
(i thought turning off autoscale affected the whole chart [not just the font] so that was a bonus in less code thanks)
Sub ResizeChart(UniqFoRsArr)
Dim RowCount

RowCount = UBound(UniqFoRsArr, 1)

ActiveSheet.ChartObjects("Chart 579").Activate
ActiveSheet.Shapes("Chart 579").Height = 10 + (RowCount + 0.1) * 12.6

End Sub

Open in new window

0
 
LVL 5

Author Comment

by:TigerMan
ID: 37006280
so not sure where to go with this in awarding/points etc
i found the answer - the input wasn't overly helpful to that end ...
what now?
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 37007211
The camera function would allow you to create an image that can transferred to jpeg.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37007213
You can simply delete the question. You can find the link near the bottom right of the question box.
0
 
LVL 5

Author Comment

by:TigerMan
ID: 37009842
i don't know what happens with different ways to close a question
if Deleted then it vanishes
something in here might be useful to someone later
if i accept my own answer as a solution what happens to the points?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37009878
If you delete it vanishes. If you accept your own comment (it should be a working solution) then zero points are awarded in this question.
0
 
LVL 5

Author Closing Comment

by:TigerMan
ID: 37035317
agreed by others in the question to either delete or self-award
chose to self-award as its a mostly complete solution and might be of use to others
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question