Use this
Code:Private Sub CommandButton1_Click() Dim lng As Long, lngIndex As Long Dim strChart As String With Worksheets("Charts") If .ChartObjects.Count Then .ChartObjects.Delete End If For lng = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(lng) Then lngIndex = lngIndex + 1 With .Shapes.AddChart.Chart .Parent.Width = 300 .Parent.Height = 150 .Parent.Top = lngIndex * (.Parent.Height + 10) - .Parent.Height .Parent.Left = 10 .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) .ChartGroups(1).GapWidth = 50 strChart = .Parent.Name End With With .Shapes.AddChart.Chart .Parent.Width = 300 .Parent.Height = 150 .Parent.Top = Worksheets("Charts").ChartObjects(strChart).Top .Parent.Left = Worksheets("Charts").ChartObjects(strChart).Left + Worksheets("Charts").ChartObjects(strChart).Width + 10 .ChartType = xlColumnClustered .SetSourceData Source:=Worksheets("DATA").Range("" & Worksheets("DATA").Range(Replace(Me.ListBox1.List(lng), "-", "")).Cells(4).Resize(4).Address & "," & Worksheets("DATA").Range(Replace(Me.ListBox1.List(lng), "-", "")).Cells(4).Resize(4).Offset(, Me.ComboBox1.ListIndex + 1).Address) .ChartGroups(1).GapWidth = 50 End With With .Shapes.AddChart.Chart .Parent.Width = 300 .Parent.Height = 150 .Parent.Top = Worksheets("Charts").ChartObjects(strChart).Top .Parent.Left = Worksheets("Charts").ChartObjects(strChart).Left + Worksheets("Charts").ChartObjects(strChart).Width * 2 + 20 .ChartType = xlColumnClustered .SetSourceData Source:=Worksheets("DATA").Range("" & Worksheets("DATA").Range(Replace(Me.ListBox1.List(lng), "-", "")).Cells(10).Resize(3).Address & "," & Worksheets("DATA").Range(Replace(Me.ListBox1.List(lng), "-", "")).Cells(10).Resize(3).Offset(, Me.ComboBox1.ListIndex + 1).Address) .ChartGroups(1).GapWidth = 50 End With End If Next lng End With Application.Goto Worksheets("Charts").Range("A1") Unload Me End Sub




Reply With Quote
Bookmarks