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.....