Dynamic Name for "Chart" in VBA

[hemants]

Morning All,

Please Login or Register  to view this content.

using a macro to create a chart but noticed that the static names prevents me from testing it over and over again.

also this code refers to the part where i re-size the chart to fit the printable area.

any ideas?

[Andy Pope]

If there is only 1 chart on the worksheet then

Please Login or Register  to view this content.

If you create the chart with VBA then give it a meaningful name via code.

[hemants]

thx for the code

need 2 more things:
1. how to name chart in vba code
2. how to delete that chart

[Andy Pope]

How do you add the chart? Show us your code.

[hemants]

i took the code straight from "record macro"

Please Login or Register  to view this content.

i made the adjustment - removed Shapes("Chart 5") and replaced with ChartObjects(1)

[Andy Pope]

Something like the following,

Give the activechart's parent a fixed name.

Please Login or Register  to view this content.

delete a chart with that fixed name

Please Login or Register  to view this content.

[hemants]

thx Andy Pope,

you adjust work perfectly with my existing code but now I run into a new problem

getting a runtime error 1004 at the beginning of the following code

Please Login or Register  to view this content.

any way to adjust the code?

[Andy Pope]

Does series 1 have data labels that you can select?

Selection of elements in a chart is not normally required.

Please Login or Register  to view this content.

[hemants]

yes, when selecting the range values the heading for each field is as follows

Cable - would be considered SeriesCollection(1)
DSL - would be considered SeriesCollection(2)
Dial - would be considered SeriesCollection(3)

[hemants]

Does series 1 have data labels that you can select?

Selection of elements in a chart is not normally required.

Please Login or Register  to view this content.

i get errored out on the above line when running those commands

[Andy Pope]

when selecting the range values

You mention range values. The code suggests selection of data labels. If the data labels do not exists, and there is no code to suggest they do, you will not be able to select them.

Can you post example workbook?

[hemants]

Hi Andy,

i excluded some secure info and just provided the sheets that are being used

VBA code is in the "Control" Sheet
Chart info is in the "Data" Sheet
Chat location is in "BiWeekly Pg8" Sheet

Please Login or Register  to view this content.

[Andy Pope]

As I said previously you can not select data labels unless you have applied data labels. You code does not apply datalabels.

you need to add a line to apply them

Please Login or Register  to view this content.

[hemants]

Hi Andy,

that line was exactly what I needed to make the macro work

ActiveChart.ApplyDataLabels

it runs through properly with no runtime error