Hi,
Something like this.
The function returns a chart object, if found. It will search for chart
sheets is the chartname parameter is empty. Otherwise it will search for
a chart object on the named sheet.
'----------------
Sub X()
Dim strChartName As String
Dim strSheetName As String
Dim chtMyChart As Chart
strSheetName = "Sheet1"
strChartName = "Chart 1"
Set chtMyChart = ChartByName(strSheetName, strChartName)
If chtMyChart Is Nothing Then
MsgBox "Chart Not Found", vbExclamation
Else
MsgBox chtMyChart.Parent.Name
End If
strSheetName = "Chart1"
strChartName = ""
Set chtMyChart = ChartByName(strSheetName, strChartName)
If chtMyChart Is Nothing Then
MsgBox "Chart Not Found", vbExclamation
Else
MsgBox chtMyChart.Name
End If
End Sub
Function ChartByName(SheetName As String, ChartName As String) As Chart
'
' If ChartName is empty assum chart sheet rather than chart object
'
On Error GoTo ErrChartByName
If Len(ChartName) = 0 Then
' chart sheet
Set ChartByName = Charts(SheetName)
Else
Set ChartByName = _
Sheets(SheetName).ChartObjects(ChartName).Chart
End If
Exit Function
ErrChartByName:
Set ChartByName = Nothing
Exit Function
End Function
'-------------------------
Cheers
Andy
sas wrote:
Hi,
Can some one help me to "Select charts by Names".
- I have multiple charts with different names in multiple work
sheets.
- These must be pasted to different slides in ppt.
- I planned to have worksheet names, chart names, slide nos. and chart
size in columns.
- So I want a syntax to select charts by names.
Thanks,
SAS.
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info