Is it possible to change the location of the data label at runtime?
In Excel VBA we use just the code like this:
For j = 1 To oSeries.Points.count
dblAmount = 0
For i = 1 To countSeries
Set oSeriesWrk = oChart.SeriesCollection(i)
dblAmount = dblAmount + oSeriesWrk.Values(j)
dblBudget = dblBudget + dblAmount
Set oDataLabel = oSeries.Points(j).DataLabel
oDataLabel.Characters.Text = FormatNumber(dblAmount, 0, , vbTrue)
oDataLabel.Top = oChart.Axes(xlValue).Top _
+ oChart.Axes(xlValue).Height * (1 - dblAmount _
/ (oChart.Axes(xlValue).MaximumScale _
- oChart.Axes(xlValue).MinimumScale)) _
- 1.8 * oDataLabel.Font.Size
I have tried to make the template and set the location of the label there but after the data filling the position of the data labels are not the same as it was in template.
Please check the links for the pictures
For some reason the links do not work properly.
Sadly there isn't a method to manually set label positions, but one reason for that is that you normally don't want manual positions. If you manually position the labels, they will be wrong when if you change the data.
While with automatic label position, they will follow the data (in case it doesn't animate, the image above is a gif):
Now, I am not sure on why you get the positions you show in the screenshots posted. Can you check by right clicking in a label in the template, then choosing "format data labels" and make sure they are in something like "outside end"? To make sure, select a different one like "center", then select back outside end (Excel will show the labels as automatic even if they are manual, so the simplest way to ensure they are automatic is to change them to a different setting and then back).
The setting should look like this:
If they are already in the "outside end", can you email me the template and generated report to firstname.lastname@example.org
so I can investigate more what is going on?
Thank you for your answer. Unfortunately for the template that we have this option "outside end" is not available.
Seems that it is not for all types of the chart possible to use it. We will check it, maybe we can change the template to get this option.
You are right. We changed the template to the "Clastered Column" and it works exactly as we need.
One more question. Seems that our customer does not agree to change "Stacked Column" to "Clastered Column" and the position of the labels should be on the top of the columns (please check the picture below)
Can you make some proposal how we can fit this requirements? We thought before that maybe we will use VBA code to move these labels but seems that by security reasons VBA macros will be not available on the customer PC.
I am trying to figure out a little better what can be best done here.
From what I see in the image and the VB code, I think you aren't using actually the labels as series labels, but just as textboxes? I mean, normal labels are per series, so in a stacked chart they look like this:
This is the reason stacked charts don't have an option to put the labels at the top: The label for the blue series would get mixed with the labels in the orange and grey series.
Now, what I think you are doing here with VBA is to take just the labels in one series, move it to the top, and change its text so it shows the sum of all labels, not just the value of that particular label.
If this is the case, wouldn't it make more sense not to use labels at all, and just add textboxes? (like the "Text1" box I added in the image above the third column). You will have to manually figure out how to place those boxes and the text you want to put inside, but you are doing this already with the labels and VBA.
If adding textboxes to the chart is an option that can make sense, we can add a method AddAutoShape to the TXlsChart option, which would be similar to the AddAutoShape method in TXlsFile, but for a chart. Could this be an option?
Also as to figure out some extra details, are the files you are using xls or xlsx? And is the number of columns fixed?
You are right. This label is not 100% pure label because we show the summary of all values in the column. For that reason we calculate it first (sum) in VBA coed and after thet replace the value of the last seria with the summary.
Your idea about text boxes is very good. The only one problem here that the number of the text boxes are dynamic and the position should be on the top. As I know it is not possible to create text boxes bynamically and set the position exactly at the top of the column.
P.S. We are using xls format because some functionality with chart does not work with xlsx format (at least it was something like that).
Is it possible to add text boxes at run time and set the location
Sorry, some text was skipped.
I hope you can help us to find some solution for our issue. Seems that the requirement is really not typical,,,,
Adding a textbox to a chart is not currently possible, but it was not (too) difficult to implement and we have already added it here. FlexCel 6.6.17 coming next week will have an AddAutoShape method in charts to add them.
Now, the issue remaining would be calculating the position of those textbox. We do all the calculations when we render the chart, so we know where the labels have to go, but this is not exposed in a public way, and I am not sure on what would be a good api for that. I'll think a little more about it and will let you know.
Thank you, we will wait your feedback.
just a quick follow up. Can you share with me when 6.6.17 will be published (this week Friday)?
Did you or your colleagues find a solution/workaround for position topic ?
Thx in advance for your expertise and feedback.
6.6.17 should have been already out, but it normally happens that when we are getting near to release it seems to be an increase in support from many customers which tend to delay it a little. Right now, I am looking at your other post about insertandcopysheets for chart sheets, and I would like to make sure there isn't anything fast we can do to include it in this release instead of delaying it for later.
About the position issue, as said we have implemented the addautoshape support and it is working well, but I haven't got time to look into something for the position. Again, this is other of the things that is delaying 6.6.17 but I also have about 3 or 4 issues to review from other customers that I would love to see if they can be implemented for this release.
Also 6.6.17 is a big release, including full support for Excel 2007 tables, support for "strict xlsx" files and support for formula expressions. This means a lot of testing and small issues and polishing. We normally prefer to delay a little the release until everything is right than to rush semi implemented features.