Listing Seaching for Charts Using the Chart Title

' searches charts on a worksheet by chart title

Function GetChartByCaption(ws As Worksheet, sCaption As String) _ As Chart

Dim chtObj As ChartObject Dim cht As Chart Dim sTitle As String

Set cht = Nothing

' loop through all chart objects on the ws For Each chtObj In ws.ChartObjects

' make sure current chart object chart has a title If chtObj.Chart.HasTitle Then sTitle = chtObj.Chart.ChartTitle.Caption

' is this title a match?

If StrComp(sTit1e, sCaption, vbTextCompare) = 0 Then

' bingo

Set cht = chtObj.Chart Exit For End If

End If

Next

Set GetChartByCaption = cht Set cht = Nothing Set chtObj = Nothing

End Function

Sub TestGetChartByCaption() Dim cht As Chart Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Basic Chart")

Set cht = GetChartByCaption(ws, "I am the Chart Title")

If Not cht Is Nothing Then MsgBox "Found chart"

Else

MsgBox "Sorry - chart not found" End If

Set ws = Nothing Set cht = Nothing End Sub

This function works by looping through all of the ChartObjects and their associated charts on the specified worksheet. For each chart, it checks to make sure the chart has a title, and if it does, the function checks the title for a match. The TestGetChartByCaption procedure provides an example of how you could use the GetChartByCaption function. Basically, all you need to do is make sure that a chart was found by comparing the result to Nothing.

Once you've found a chart to manipulate, the actual manipulation is quite easy now that you're familiar with what I've covered earlier in the chapter regarding the use of colors, the Font object, the Interior object, and the Border object. Listing 10.12 provides a basic example of some chart formatting.

After looking over this listing, I am quite confident that you'll recognize the chart objects and the chart elements to which they apply as shown in Figure 10.14. Also, notice how many of the chart objects, being visual in nature, use the various objects that I covered earlier in the chapter.

0 0

Post a comment