Using VBA to create charts with data labels based on cell values

Question

I'm completely new to VBA, and would appreciate some guidance. I would like to use a macro that, on a button press, creates a chart

Based on the range selected by the user (shown in the image below)
On a new worksheet
With x-axis data labels being set to the top row of headings (the blue range)
With series labels being set according to the three group labels immediately to the left of the data. (the orange range)

So far, all I've succeeded doing is the first one, based on this answer, resulting in the following code:

Sub CommandButton1_Click()
    createChart
End Sub

Sub createChart()
    Dim myRange As Range
    Set myRange = Selection

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=myRange

    Cells(1, 1).Select
End Sub

Can anyone advise me how I can change this code to create the chart on a new worksheet, and using the data and series labels in a given range of cells? If need be, I can separate these into different questions. Thank you.

The first thing to do in these cases is to record a macro while doing what you just described. That will result in some code that you can put in your own code. — Sam
– Sam, Commented May 9, 2018 at 18:41
Probably something like ActiveChart.Location Where:=xlLocationAsObject, Name:="{SheetName}" — ccarpenter32
– ccarpenter32, Commented May 9, 2018 at 18:48
@Sam That's really helpful, I've worked out using your method how to dynamically format the axis labels, series labels and title. What I still can't work out is how to put the chart on a separate worksheet. — Lou
– Lou, Commented May 9, 2018 at 19:15
@Jiggles32 I tried the code you suggested, but it only works for existing sheets. I want to create a new sheet and put the chart on there. — Lou
– Lou, Commented May 9, 2018 at 19:15
I think ActiveChart.Location Where:=xlLocationAsNewSheet is what you want — Sam
– Sam, Commented May 9, 2018 at 19:35

Lou · Accepted Answer · 2018-05-09 19:55:18Z

Thanks to Sam, I was able to work out the code I needed.

Sub createChart()

    Dim myRange As Range
    Set myRange = Selection

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=myRange

    Dim SheetName As String
    SheetName = Cells(myRange.Row, 2).Value
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=SheetName

    ActiveChart.FullSeriesCollection(1).XValues = "=Scores!$E$1:$I$1"

    Dim i As Integer

    For i = 1 To ActiveChart.FullSeriesCollection.Count
        ActiveChart.FullSeriesCollection(i).Name = "=Scores!$C" & myRange.Row + i - 1
    Next

    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "=Scores!$A" & myRange.Row

End Sub

This lets me select the range, click the button, and the chart is created in a new sheet with all of the data labels I need. This should really help me out next time I need to create a chart-based macro.

Collectives™ on Stack Overflow

Using VBA to create charts with data labels based on cell values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related