No Chart Titles

  • Thread starter Michael Hudston
  • Start date
M

Michael Hudston

Has anyone got any idea's why my Charts are not displaying any titles?

I would be apreciative of any suggestions

Thanks

Mike

Code Below

Sub Draw_Chart_Click()

On Error Resume Next

Dim chtChart As Chart
Dim Pt As Point
Dim Ser
Dim arr
Dim arr2
Dim i
Dim chtSlct As String

chtSlct = Trim(Range("B5").Value)

' Remove Existing Chart

ActiveSheet.ChartObjects.Delete

' Create a new chart.

arr = Array(RGB(83, 142, 213), RGB(149, 179, 213), RGB(217, 151, 149),
RGB(194, 214, 154), RGB(178, 161, 199), RGB(147, 205, 221), RGB(250, 192,
144), RGB(23, 55, 93), RGB(55, 96, 145), RGB(149, 55, 53), RGB(117, 146, 60),
RGB(96, 73, 123))
i = -1


Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject,
Name:="CHARTS")

' Choose Which Chart to Plot
If chtSlct = Range("B101").Value Then

' Set Description
Range("B16").Value = Range("H101").Value

' Set Colour Scheme for Chart

With chtChart

' Set Chart Type
.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("L11:X14"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Incident Age (From Occurence to Closure)"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$M$4:$X$10"

.SeriesCollection(1).Interior.Color = RGB(0, 255, 0)
.SeriesCollection(2).Interior.Color = RGB(255, 255, 0)
.SeriesCollection(3).Interior.Color = RGB(255, 180, 0)
.SeriesCollection(4).Interior.Color = RGB(255, 0, 0)

' The Parent property is used to set properties of the Chart, in
this case the location on the sheet.

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height

End With
End With

ElseIf chtSlct = Range("B100").Value Then

' Set Description
Range("B16").Value = Range("H100").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("K5:V5,
K2006:V2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Number of Incidents by LRU"

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B102").Value Then

' Set Description
Range("B16").Value = Range("H102").Value

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART
DATA").Range("H76:I79"), PlotBy:=xlRows
.ChartTitle.Text = "Severity (SRB V User)"
.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$H$75:$I$75"

.SeriesCollection(1).Name = "='BASIC CHART DATA'!$G$76"
.SeriesCollection(2).Name = "='BASIC CHART DATA'!$G$77"
.SeriesCollection(3).Name = "='BASIC CHART DATA'!$G$78"
.SeriesCollection(4).Name = "='BASIC CHART DATA'!$G$79"

.SeriesCollection(1).Interior.Color = RGB(255, 0, 0) 'Critical
.SeriesCollection(2).Interior.Color = RGB(255, 180, 0) ' Major
.SeriesCollection(3).Interior.Color = RGB(255, 255, 0) ' Minor
.SeriesCollection(4).Interior.Color = RGB(0, 255, 0) ' Not
Relevant

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

ElseIf chtSlct = Range("B103").Value Then

' Set Description
Range("B16").Value = Range("H103").Value


With chtChart

.ChartType = xlCylinderCol
' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("H49:H60")
.HasTitle = True
.ChartTitle.Text = "Incidents by Cause"

.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$G$49"
.SeriesCollection(1).Name = "='BASIC CHART DATA'!$G$49"
.SeriesCollection(2).XValues = "='BASIC CHART DATA'!$G$50"
.SeriesCollection(2).Name = "='BASIC CHART DATA'!$G$50"
.SeriesCollection(3).XValues = "='BASIC CHART DATA'!$G$51"
.SeriesCollection(3).Name = "='BASIC CHART DATA'!$G$51"
.SeriesCollection(4).XValues = "='BASIC CHART DATA'!$G$52"
.SeriesCollection(4).Name = "='BASIC CHART DATA'!$G$52"
.SeriesCollection(5).XValues = "='BASIC CHART DATA'!$G$53"
.SeriesCollection(5).Name = "='BASIC CHART DATA'!$G$53"
.SeriesCollection(6).XValues = "='BASIC CHART DATA'!$G$54"
.SeriesCollection(6).Name = "='BASIC CHART DATA'!$G$54"
.SeriesCollection(7).XValues = "='BASIC CHART DATA'!$G$55"
.SeriesCollection(7).Name = "='BASIC CHART DATA'!$G$55"
.SeriesCollection(8).XValues = "='BASIC CHART DATA'!$G$56"
.SeriesCollection(8).Name = "='BASIC CHART DATA'!$G$56"
.SeriesCollection(9).XValues = "='BASIC CHART DATA'!$G$57"
.SeriesCollection(9).Name = "='BASIC CHART DATA'!$G$57"
.SeriesCollection(10).XValues = "='BASIC CHART DATA'!$G$58"
.SeriesCollection(10).Name = "='BASIC CHART DATA'!$G$58"
.SeriesCollection(11).XValues = "='BASIC CHART DATA'!$G$59"
.SeriesCollection(11).Name = "='BASIC CHART DATA'!$G$59"
.SeriesCollection(12).XValues = "='BASIC CHART DATA'!$G$60"
.SeriesCollection(12).Name = "='BASIC CHART DATA'!$G$60"

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

ElseIf chtSlct = Range("B104").Value Then

' Set Description
Range("B16").Value = Range("H104").Value

With chtChart

.ChartType = xlCylinderCol
' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("H63:H66")
.HasTitle = True
.ChartTitle.Text = "Number of Incidents by Liability"

.SeriesCollection(1).XValues = "='BASIC CHART DATA'!$G$63"
.SeriesCollection(1).Name = "='BASIC CHART DATA'!$G$63"
.SeriesCollection(1).Interior.Color = RGB(255, 0, 0)
.SeriesCollection(2).XValues = "='BASIC CHART DATA'!$G$64"
.SeriesCollection(2).Name = "='BASIC CHART DATA'!$G$64"
.SeriesCollection(2).Interior.Color = RGB(0, 255, 0)
.SeriesCollection(3).XValues = "='BASIC CHART DATA'!$G$65"
.SeriesCollection(3).Name = "='BASIC CHART DATA'!$G$65"
.SeriesCollection(3).Interior.Color = RGB(0, 0, 255)
.SeriesCollection(4).XValues = "='BASIC CHART DATA'!$G$66"
.SeriesCollection(4).Name = "='BASIC CHART DATA'!$G$66"
.SeriesCollection(4).Interior.Color = RGB(255, 255, 0)

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

ActiveChart.Axes(xlCategory).Select
Selection.Delete

ElseIf chtSlct = Range("B105").Value Then

' Set Description
Range("B16").Value = Range("H105").Value

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("G34:H39")

.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection.XValues = "='BASIC CHART DATA'!$G$34:$G$39"
.SeriesCollection.Name = "='BASIC CHART DATA'!$G$34:$G$39"

.SeriesCollection(1).Interior.Color = RGB(255, 0, 0) 'Open
.SeriesCollection(2).Interior.Color = RGB(255, 180, 0) ' Under
Investigation
.SeriesCollection(3).Interior.Color = RGB(0, 128, 0) 'Corrective
Action
.SeriesCollection(4).Interior.Color = RGB(255, 0, 0) ' Deferred
.SeriesCollection(5).Interior.Color = RGB(128, 255, 0) ' SRB
.SeriesCollection(6).Interior.Color = RGB(0, 255, 0) ' Closed

ActiveChart.Axes(xlCategory).Select
Selection.Delete

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height

End With
End With

ElseIf chtSlct = Range("B106").Value Then

' Set Description
Range("B16").Value = Range("H106").Value

With chtChart

.ChartType = xlCylinderCol

' Set data source range.

.SetSourceData Source:=Sheets("BASIC CHART DATA").Range("G34:H39")

.HasTitle = True
.ChartTitle.Text = "Current Status"
.SeriesCollection.XValues = "='BASIC CHART DATA'!$G$34:$G$39"
.SeriesCollection.Name = "='BASIC CHART DATA'!$G$34:$G$39"

.SeriesCollection(1).Interior.Color = RGB(255, 0, 0) 'Open
.SeriesCollection(2).Interior.Color = RGB(255, 180, 0) ' Under
Investigation
.SeriesCollection(3).Interior.Color = RGB(0, 128, 0) 'Corrective
Action
.SeriesCollection(4).Interior.Color = RGB(255, 0, 0) ' Deferred
.SeriesCollection(5).Interior.Color = RGB(128, 255, 0) ' SRB
.SeriesCollection(6).Interior.Color = RGB(0, 255, 0) ' Closed

ActiveChart.Axes(xlCategory).Select
Selection.Delete

' The Parent property is used to set properties of the Chart.

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height

End With
End With

ElseIf chtSlct = Range("B110").Value Then

' Set Description
Range("B16").Value = Range("H110").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("Y5:AH5,
Y2006:AH2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "SSR+ 400 Mhz Radio Incidents by Analysis"

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B111").Value Then

' Set Description
Range("B16").Value = Range("H111").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("AK5:AL5,
AK2006:Al2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Antenna, High Gain - Incidents by Analysis"

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B112").Value Then

' Set Description
Range("B16").Value = Range("H112").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("AP5:AQ5,
AP2006:AP2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "GPS Antenna - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B113").Value Then

' Set Description
Range("B16").Value = Range("H113").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("AT5:AU5,
AT2006:AU2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "AA Battery Carrier - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B114").Value Then

' Set Description
Range("B16").Value = Range("H114").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("AX5:AX5,
AX2006:AX2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Pouch, DPM - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B115").Value Then

' Set Description
Range("B16").Value = Range("H115").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("BB5:BB5,
BB2006:BB2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Team Member Headset - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B116").Value Then

' Set Description
Range("B16").Value = Range("H116").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("BF5:BG5,
BF2006:BG2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Wireless PTT (Dual) - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B117").Value Then

' Set Description
Range("B16").Value = Range("H117").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("BF5:BG5,
BF2006:BG2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Dual Radio Switch Box - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B118").Value Then

' Set Description
Range("B16").Value = Range("H118").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("BK5:BL5,
BK2006:BL2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "USB Cable Assy - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B119").Value Then

' Set Description
Range("B16").Value = Range("H119").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("BS5:BY5,
BS2006:BY2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Network Planning Tool - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B120").Value Then

' Set Description
Range("B16").Value = Range("H120").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("CB5:CD5,
CB2006:CD2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Key Generation Tool - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

ElseIf chtSlct = Range("B121").Value Then

' Set Description
Range("B16").Value = Range("H121").Value

With chtChart

.ChartType = xlCylinderColClustered
' Set data source range.

.SetSourceData Source:=Sheets("Trend Analysis").Range("CG5:CH5,
CG2006:CH2006"), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Radio Loader - Incidents by Analysis"
With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

For Each Pt In chtChart.SeriesCollection(1).Points
i = i + 1
If i <= UBound(arr) Then Pt.Interior.Color = arr(i)
Next

Else

' Set Description
Range("B16").Value = Range("H122").Value

With chtChart
.ChartType = xlCylinderColClustered
' Set data source range.

.HasTitle = True
.ChartTitle.Text = "INVALID CHART"

With .Parent
.Top = Range("G2").Top
.Left = Range("G2").Left
.Width = Range("G2:S31").Width
.Height = Range("G2:S31").Height
End With
End With

MsgBox "Invalid Chart Selected - Please Choose Another"
End If

' Remove Legend

ActiveChart.Legend.Select
Selection.Delete

End Sub
 
J

Joel

You are getting errors. Remove the statement "On Error Resume Next" and try
running your code again. The errors are your problem. Also change your VBA
setting in the menu Tools - Options - general - Stop on all errors. This
should help you find the real problems.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Setting the Colour of Individual Items within a Legend 6
How do I set the Maximum Property for this Chart in code 1
controlling chart location 1
Showing charts with hidden data 4
Create several charts in one go using VBA 4
Setting chart Xvalue errors 5
Creating Charts using VBA 0
how to add error bars into bar chart 1

Top