Change Chart Type via VBA

[Access Beginner]

Hi All

I have a few charts in a workbook, which I export to Word. One Manager likes these charts as a line chart and another Manager likes these as column charts.

I've recorded two macros to change to a line chart and change to a column chart.

Regarding the 1st macro (changing to line chart) I was unable to record the alignment of the data labels, can someone please include the code to change the alignment to -27?

Secondly can someone somehow tweak the code, so that I can specify which charts I want to change to a line chart ie Chart 5, Chart 6 etc and also do the same for the change to Colum Chart code?



Code to change to a line chart

Sub Change_Chart_Line()
'Change charts specified to line chart
    Sheets("Dashboard Trend (2)").Select
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.ChartType = xlLine
    ActiveSheet.ChartObjects("Chart 5").Activate    
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.Position = xlLabelPositionAbove
       
End Sub

Code to change to column chart

Sub Change_Chart_Column()
'
' Change charts to column chart'
'
    Sheets("Dashboard Trend (2)").Select
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.Position = xlLabelPositionInsideBase
    
    
End Sub

Cheers
Haydn

 

[Access Beginner]

Hi all,

I worked out how to align the data labels. When I used the recorder the alignment option as greyed out, but I tried again and set it to horizontal, which then allowed me to set an alignment value.. Selection.Orientation = 27.

Can someone please still look at 2nd part of my request which is to tweak the code?

Cheers
Haydn