Editing Chart Data Labels in Excel 2007 using VBA

[NukedWhale]

Hello VBA Express! I'm new to this forum so bear with me. :hi:

I have an Excel 2007 workbook with many worksheets and 3 stacked bar charts per worksheet. I'm pretty inexperienced with VBA so I might need a little handholding.

I'm trying to write a macro that will resize all charts and change the position of data labels for 2 series within the charts (I already have bad labels in place so labels may need to be deleted then reapplied).

This is my current loop to resize charts...how do I include the code to mess with data labels within my loop?

Sub Resize()

Dim iChart As Long
Dim nCharts As Long

Let nCharts = ActiveSheet.ChartObjects.Count

For iChart = 1 To nCharts
With ActiveSheet.ChartObjects(iChart)
.Height = 480
End With
Next

End Sub

This is sort of what the macro recorder code looked like for positioning the data labels the way I wanted to.

SeriesCollection(2).DataLabels = msoElementDataLabelInsideBase
SeriesCollection(1).DataLabels = msoElementDataLabelInsideEnd

Thanks in advance,
NukedWhale

[Bob Phillips]

I think that you need to post a workbook for us to see.

What happened with that recorded code above?

[NukedWhale]

The resize macro loops through all charts in an active worksheet and increases their height.

I need to break my data labels questions down into more simple questions.

Can the With function be used for SeriesCollections in charts?

[Bob Phillips]

The resize macro loops through all charts in an active worksheet and increases their height.

I need to break my data labels questions down into more simple questions.

I am not sure that I understand that or what it means here?


Can the With function be used for SeriesCollections in charts?

Of course you can, With is just an implict object declaration, but what does that do to help achieve your objectives? It might be used, but might not, but is not a solution in its own rigt.