Change Chart Title According To Source Data Selection

[terrybloome]

What is required to change in the Chart code so that the charts have the below

1. Y-Axis showing the Hours
2. Each column showing the Name underneath and the Value on top of the column

Regards

Terry

[Excel Fox]

Replace the original code in the userform with this

Private Sub CommandButton1_Click()

    Dim lng As Long
    Dim ser As Series
    
    For lng = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(lng) Then
            With Worksheets("Charts")
                With .Shapes.AddChart.Chart
                    .Parent.Top = Worksheets("Charts").ChartObjects.Count * .Parent.Height + 10 - .Parent.Height
                    .ChartType = xlColumnClustered
                    .SetSourceData Source:=Worksheets("DATA").Range("" & Worksheets("DATA").Range(Replace(Me.ListBox1.List(lng), "-", "")).Address & "," & Worksheets("DATA").Range(Replace(Me.ListBox1.List(lng), "-", "")).Offset(, Me.ComboBox1.ListIndex + 1).Address)
                     .Parent.Width = .Parent.Width * 2
                    .ChartGroups(1).GapWidth = 50
                    .ApplyDataLabels
                    For Each ser In .SeriesCollection
                        With ser.DataLabels
                            .ShowSeriesName = True
                            .Position = xlLabelPositionInsideBase
                            .Separator = "" & Chr(10) & ""
                        End With
                    Next ser
                End With
            End With
        End If
    Next lng
    Application.Goto Worksheets("Charts").Range("A1")
    Unload Me
    
End Sub

[Excel Fox]

In fact, use this one for the vertical axis title also

Private Sub CommandButton1_Click()

    Dim lng As Long
    Dim ser As Series
    
    For lng = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(lng) Then
            With Worksheets("Charts")
                With .Shapes.AddChart.Chart
                    .Parent.Top = Worksheets("Charts").ChartObjects.Count * .Parent.Height + 10 - .Parent.Height
                    .ChartType = xlColumnClustered
                    .SetSourceData Source:=Worksheets("DATA").Range("" & Worksheets("DATA").Range(Replace(Me.ListBox1.List(lng), "-", "")).Address & "," & Worksheets("DATA").Range(Replace(Me.ListBox1.List(lng), "-", "")).Offset(, Me.ComboBox1.ListIndex + 1).Address)
                     .Parent.Width = .Parent.Width * 2
                    .ChartGroups(1).GapWidth = 50
                    .ApplyDataLabels
                    .SetElement (msoElementPrimaryValueAxisTitleRotated)
                    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Hour"
                    For Each ser In .SeriesCollection
                        With ser.DataLabels
                            .ShowSeriesName = True
                            .Position = xlLabelPositionInsideBase
                            .Separator = "" & Chr(10) & ""
                        End With
                    Next ser
                End With
            End With
        End If
    Next lng
    Application.Goto Worksheets("Charts").Range("A1")
    Unload Me
    
End Sub

[terrybloome]

Thanks

Would it be achievable to create the charts like the attached workbook....if I select Team 1 in userform I get the 3 charts across the sheet for Team 1...the same for Team 2 and Team 3....Is the layout achievable with small gap between the charts plus the date at the top,labels under each column etc..etc.....

Thanks

Terry

[terrybloome]

Any thoughts.....