Moving around chart series legend via VBA

war4peace

New Member
Joined
Mar 13, 2009
Messages
21
I have a calls capacity excel macro that generates a report depending on a shift. The report is a chart that is generated via the following code:

Code:
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range(Cells(2, 1), Cells(6, crumbles))
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.Legend.Select
    ActiveChart.SeriesCollection(1).Name = "S1"
    ActiveChart.SeriesCollection(2).Name = "S2"
    ActiveChart.SeriesCollection(3).Name = "S3"
    ActiveChart.SeriesCollection(4).Name = "S4"
    ActiveChart.SeriesCollection(5).Name = "S5"
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.Position = xlLabelPositionAbove
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).ApplyDataLabels
    ActiveChart.SeriesCollection(3).DataLabels.Select
    Selection.Position = xlLabelPositionAbove
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ApplyDataLabels
    ActiveChart.SeriesCollection(2).DataLabels.Select
    Selection.Position = xlLabelPositionAbove
    ActiveChart.SeriesCollection(4).Select
    ActiveChart.SeriesCollection(4).ApplyDataLabels
    ActiveChart.SeriesCollection(4).DataLabels.Select
    Selection.Position = xlLabelPositionAbove
    ActiveChart.SeriesCollection(5).Select
    ActiveChart.SeriesCollection(5).ApplyDataLabels
    ActiveChart.SeriesCollection(5).DataLabels.Select
    Selection.Position = xlLabelPositionAbove
    ActiveChart.SeriesCollection(1).XValues = Range(Cells(1, 1), Cells(1, crumbles))
    ActiveChart.Axes(xlCategory).HasMajorGridlines = True
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=ShiftName + "_excel"
    ActiveWorkbook.PublishObjects.Add(xlSourceChart, "D:\www\scheduler\" & ShiftName & ".htm", ShiftName + "_excel", "", xlHtmlStatic, _
                                    ShiftName & "W4P_ID", ShiftName & " Calls Capacity" _
                                    & " (last refresh at: " & Now() & ")").Publish (True)

What I need to find out is how can I set the Series (S1, s2... etc) legend to be displayed at the BOTTOM of the graph instead of at the right of the graph (where it is located by default) in VBA.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I have performed some research but maybe I am not searching for the appropriate thing... Anyone able to at least point me to the right direction?
 
Upvote 0
If your chart is selected, this code will move the Legend to the bottom:
Code:
    ActiveChart.Legend.Position = xlBottom
 
Upvote 0
I have performed some research but maybe I am not searching for the appropriate thing... Anyone able to at least point me to the right direction?

LE: Nevermind, I got it :)
ActiveChart.Legend.Position
Duh! :)

LLE: DatSmart, we posted in pretty much the same time. Thank you for the reply!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top