Printable View
Hi
I am stuck at this point and need help please
I need some help combining the Userform (workbook - Charts Userform) and its code to place the Charts in position exactly like the charts in workbook - Charts-007
I have the Charts appearing in Charts-007 but I need them to appear in the Sheet called CHARTS and not in the Sheet DATA.I would like to use the Userform and code though to place these Charts
Regards
Terry
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
Thank you
I am still learning with macro recorder but your code is superior to what I know so I was unable to change a couple of parts to the charts..I tried to change the chart order in the code but have error as I see the first chart code part is different to the next 2 charts code...I attached the workbook with the charts
What code changes do I need so as chart 2 is first followed by Chart 1 and the 3rd chart is correct in order ?
The first Chart I need to appear the same as the 2nd and 3rd chart.What changes are needed in the code so it shows just Process Time & Planned Stops & Unplanned Stops....?
Can the legends be removed from the charts?
NOTE: where can I find learning material like the code you use please?
Thanks