Unable to set HasTitle property of the Chart class

hcl-bot · December 23, 2008, 6:53am

Hi All ,

Please see the below code .I have debug the code but not able to find the problem .Can anyone please help me.

Sub drawRisk(excel As Variant, prior As String)

	Dim rthis, rlast As Variant	

	Dim s As Variant

	Dim i As Integer

	Dim iserie As Integer

	On Error Goto ErrorHandler

	With excel.officeappl

		Set s = .sheets("PrioScen")			

		.Charts.Add(.sheets(5))	

		If prior = "Yes" Then

			Set rthis =  s.Range(s.cells(5,2),s.cells(5+irowmax,2))										'this year risk data

			Set rlast =  s.Range(s.cells(5,7),s.cells(5+irowmax,7))										'last year risk data

			.ActiveSheet.Name = "PrioCat"		

		Else

			Set rthis =  s.Range(s.cells(5,4),s.cells(5+irowmax,4))										'this year risk data

			Set rlast =  s.Range(s.cells(5,9),s.cells(5+irowmax,9))										'last year risk data

			.ActiveSheet.Name = "NonPrioCat"		

		End If

		Print "Create diagram " + .ActiveSheet.Name

		With excel.ActiveWorkbook.ActiveSheet.ActiveChart					

			.SizeWithWindow = True	

			.ChartType = 51		

			.HasTitle = True

			If prior = "Yes" Then

				.ChartTitle.Text = "Prioritized Risk Scenarios per Risk Category"

			Else

				.ChartTitle.Text = "Non Prioritized Risk Scenarios per Risk Category"

			End If

			Call excel.pagesetup

			With .PlotArea																								'change the background color

				With .Border

					.ColorIndex = 16

					.Weight = xlThin	

					.LineStyle = xlContinuous

				End With

				With .Interior

					.ColorIndex = 2

					.PatternColorIndex = 1	

				End With

			End With	

			Call .SetSourceData(rthis,2)																			'use this  year data serie xlColumns							

			rlast.Copy																										'add last year data serie

			Call .SeriesCollection.Paste(2,False,False, False,True)

			.ApplyDataLabels(2) 																						'show lables

			For iserie = 0 To 1																							'rework labels		

				With .SeriesCollection(iserie+1)																					

					.XValues = risks

					.Name = xValues(iserie)		

					If iserie = 0 Then

						.border.LineStyle = xlContinuous

					Else

						.border.LineStyle = xlDot

					End If

					.Interior.ColorIndex = xlNone

					For i = 0 To irowmax

						With .Points(i+1)

							.interior.ColorIndex = getRiskColorIndex(Cstr(risks(i)))							

							.Interior.Pattern = getRiskPatternIndex(Cstr(risks(i)))		

							If prior = "Yes" Then

'							.DataLabel.Characters.Text = Cstr(i) ' .DataLabel.Characters.Text  + "xxx" + Chr(13) + Format(s.cells(i+5,3).value, "0.0%")		

								.DataLabel.Text = .DataLabel.Text  + Chr(13) + Format(s.cells(i+5,3+5*iserie).value, "0.0%")					

							Else

'							.DataLabel.Characters.Text = .DataLabel.Characters.Text  + Chr(13) + Format(s.cells(i+5,5).value, "0.0%")						

								.DataLabel.Text = .DataLabel.Text  + Chr(13) + Format(s.cells(i+5,5+5*iserie).value, "0.0%")				

							End If	

						End With				

					Next

				End With				

			Next	

			

		End With

	End With

SingleExit:

	Exit Sub

ErrorHandler:

	Call ProcessError("","UICtrlPrioRisk","risktables.drawrisk",Nothing)

	Resume SingleExit

End Sub

This is not generating the required Chart. Please help.

thanks in Advance!

hcl-bot · December 24, 2008, 1:13am

Subject: Unable to set HasTitle property of the Chart class

Got the solution!!

I just moved the formatting part of code after the SeriesCollection.See the code below .its working.

Sub drawRisk(excel As Variant, prior As String)

Dim rthis, rlast As Variant

Dim s As Variant

Dim i As Integer

Dim iserie As Integer

On Error Goto ErrorHandler

With excel.officeappl

Set s = .sheets(“PrioScen”)

.Charts.Add(.sheets(5))

If prior = “Yes” Then

Set rthis = s.Range(s.cells(5,2),s.cells(5+irowmax,2)) 'this year risk data

Set rlast = s.Range(s.cells(5,7),s.cells(5+irowmax,7)) 'last year risk data

.ActiveSheet.Name = “PrioCat”

Else

Set rthis = s.Range(s.cells(5,4),s.cells(5+irowmax,4)) 'this year risk data

Set rlast = s.Range(s.cells(5,9),s.cells(5+irowmax,9)) 'last year risk data

.ActiveSheet.Name = “NonPrioCat”

End If

Print "Create diagram " + .ActiveSheet.Name

With .ActiveChart

.SizeWithWindow = True

.ChartType = 51

Call .SetSourceData(rthis,2) 'use this year data serie xlColumns

rlast.Copy 'add last year data serie

Call .SeriesCollection.Paste(2,False,False, False,True)

.ApplyDataLabels(2) 'show lables

For iserie = 0 To 1 'rework labels

With .SeriesCollection(iserie+1)

.XValues = risks

.Name = xValues(iserie)

If iserie = 0 Then

.border.LineStyle = xlContinuous

Else

.border.LineStyle = xlDot

End If

.Interior.ColorIndex = xlNone

For i = 0 To irowmax

With .Points(i+1)

.interior.ColorIndex = getRiskColorIndex(Cstr(risks(i)))

.Interior.Pattern = getRiskPatternIndex(Cstr(risks(i)))

If prior = “Yes” Then

’ .DataLabel.Characters.Text = Cstr(i) ’ .DataLabel.Characters.Text + “xxx” + Chr(13) + Format(s.cells(i+5,3).value, “0.0%”)

.DataLabel.Text = .DataLabel.Text + Chr(13) + Format(s.cells(i+5,3+5*iserie).value, “0.0%”)

Else

’ .DataLabel.Characters.Text = .DataLabel.Characters.Text + Chr(13) + Format(s.cells(i+5,5).value, “0.0%”)

.DataLabel.Text = .DataLabel.Text + Chr(13) + Format(s.cells(i+5,5+5*iserie).value, “0.0%”)

End If

End With

If prior = “Yes” Then

.ChartTitle.Text = “Prioritized Risk Scenarios per Risk Category”

Else

.ChartTitle.Text = “Non Prioritized Risk Scenarios per Risk Category”

End If

Call excel.pagesetup

With .PlotArea 'change the background color

With .Border

.ColorIndex = 16

.Weight = xlThin

.LineStyle = xlContinuous

End With

With .Interior

.ColorIndex = 2

.PatternColorIndex = 1

End With

SingleExit:

Exit Sub

ErrorHandler:

Call ProcessError(“”,“UICtrlPrioRisk”,“risktables.drawrisk”,Nothing)

Resume SingleExit

End Sub

hcl-bot · December 24, 2008, 1:19am

Subject: RE: Unable to set HasTitle property of the Chart class

thanks for helping Doug!!

hcl-bot · December 23, 2008, 12:33pm

Subject: Unable to set HasTitle property of the Chart class

I assume from your post title that the only thing wrong is that you don’t have a title on your chart.

Give this a try and see what happens.

Create a dummy XL file with some junk data.

Turn on the macro recorder and create a chart with a title.

Review the macro code - you’re looking for the format of the code around the title setup.

I’m betting you find a difference between the code you published and the code you create with the macro…

HTH

Doug