UDF Function to manipulate charts - great article needs tweaking for my purpose

[mountainman88]

I found this great article for creating a UDF to manipulate charts and it works great.

How to set chart axis based on a cell value

The chart axis min and max settings are fixed values. This posts looks at a automated method to set chart axis based on a cell value.

exceloffthegrid.com

How can I modify this to create a UDF where I can link 1) chart title where the UDF argument is a cell 2) The data range for the chart,where the UDF argument is a range of cells (this would be the same as the data source range when setting up the chart)

Thanks!

 

[mountainman88]

I think I figured it out, the only issue is the UDF resets some chart formatting.

I tried to add the following to the UDF VBA to turn the data labels on:

cht.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue

But this causes the UDF to resolve to #value.

Any ideas, I really just need to get the data labels on and this is good to go.

Thanks

 

[mountainman88]

OK i figured the datalabels out using the following line:

cht.SeriesCollection(1).HasDataLabels = True

the labels are there but I cant get them to be one decimal place format, even when using this line:

cht.SeriesCollection(1).DataLabels.NumberFormat = "##.#"

This would be the finishing touc if anyone can help.

Thanks

 

[BSALV]

are you sure your index is okay, or do you only have 1 serie ?
Seriescollection vs fullseriecollection ??? Do you sometimes select in your series ?
Normally your line should work

Sub DataNumberformat()
     With ActiveSheet.ChartObjects(1).Chart.FullSeriesCollection(1)
          .ApplyDataLabels
          .DataLabels.NumberFormat = "#.###,###"                'change to you desire
     End With
End Sub

bizar, i use dutch reginal settings and recorded & adapted the macro above.
The numberformat has a . for the thousands and a comma als decimal separator.
I check this , that can't be right.
Checked it, apparently the label numberformat follows the regional settings ???

 

[mountainman88]

are you sure your index is okay, or do you only have 1 serie ?
Seriescollection vs fullseriecollection ??? Do you sometimes select in your series ?
Normally your line should work

Sub DataNumberformat()
     With ActiveSheet.ChartObjects(1).Chart.FullSeriesCollection(1)
          .ApplyDataLabels
          .DataLabels.NumberFormat = "#.###,###"                'change to you desire
     End With
End Sub

bizar, i use dutch reginal settings and recorded & adapted the macro above.
The numberformat has a . for the thousands and a comma als decimal separator.
I check this , that can't be right.

There’s only one series.

I need the datalabel number format VBA to work within a UDF per the link in my first post. Do you think that’s the issue?

 

[BSALV]

no, but try your numberformat with a comma instead of a point

 

[mountainman88]

no, but try your numberformat with a comma instead of a point

Set cht = Application.Caller.Parent.Parent.Sheets(sheetName) _
.ChartObjects(chartName).Chart


With cht.FullSeriesCollection(1)
.HasDataLabels = True
.ApplyDataLabels
.DataLabels.NumberFormat = "##,#" 'change to you desire
End With




It has no effect, like the number format line is being ignored. .Applydatalabels actually errors the UDF to #value. I need HasDataLabels = TRUE to make the labels even appear.
Is it to do with using the wrong method for the object?

 

[BSALV]

use the macro-recorder and change the numberformat manually to "##,####".
Check what you got.
Are the chartobject (name or index) and the seriescollection-index okay ?
Change in that recorded macro, the numberformat again to "##,#" and run the macro.
Does that work ?
Is the decimal with a comma or a point ?

 

[mountainman88]

use the macro-recorder and change the numberformat manually to "##,####".
Check what you got.
Are the chartobject (name or index) and the seriescollection-index okay ?
Change in that recorded macro, the numberformat again to "##,#" and run the macro.
Does that work ?
Is the decimal with a comma or a point ?

The macro recorder only records this when I make changes to the numenr format on the chart:

ActiveSheet.Shapes.Range(Array("Chart 21")).Select

This is a waterfall chart which is one of the 'special' charts so perhaps thats the issue?

This code works to make the datalabels appear:

With cht.FullSeriesCollection(1)
.HasDataLabels = True

as soon as I add anything else like .DataLabels.NumberFormat = "##,#", the UDF errors to #Value.

 

[mountainman88]

This post seems to suggest its hard to use VBA on new chart types:

https://answers.microsoft.com/en-us/msoffice/forum/all/are-the-six-new-chart-types-in-excel-2016/3e8f9bf1-76a7-4a1a-91cd-9ed27165c17c