Zero Values in Pie Chart

[wiginprov]

I have a pie chart in an Excel template that requires input from users. A sample follows:

Bond Type Market Value
Corporate 0
Municipal 100,000
US Treasury Notes 0
US Treasury Bonds 300,000
Govt Strips 0

I need help with getting the pie chart to display just the new input. In some cases, values are entered for only one category. Having the user define the graph source range is error prone, as they are not experienced spreadsheet users.

The data labels for the pie chart must show the label and percent (i.e. Municipal 25% and US Treasury Bonds 75%. I have tried both =NA() and dynamic named ranges, without success. When I get the dynamic range to work, the labels for the zero values still display.

Any ideas?

 

[donna6197]

You could teach them to either manually delete the 0% (Ctrl click on a data label to select it and then select the text to be deleted) or stick a button somewhere that will activate a macro similar to the following:

For Each dl In ActiveChart.SeriesCollection(1).DataLabels
If dl.Text = "0%" Then
dl.Delete
End If
Next dl

 

[wiginprov]

Right now I have shown them how to redefine the source range for the graph, but I like your macro idea. That, combined with dynamic named ranges, should work. If you were to declare a variable type for dl, what would it be? I'm new to VBA.

THANKS!

 

[donna6197]

I would probably put

Dim dl as datalabel

 

[wiginprov]

Thanks. I'll try it.