Rob - one thing causing me a problem

I would like to write a function which given a sheet name as the input
parameter return a list of all charts belonging to that sheet.

When I do:
Function getChartName(sheetName As String) As String
Dim objChart As ChartObject
For Each objChart In sheetName.ChartObjects
getChartName = objChart.Name
Next objChart
End Function

It doesn't like it because sheetName is a String and not a sheet Object - I
thought about trying Function getChartName(sheetName As Worksheet) As String
but surely one can't pass a chart object into a function ?

Is there any way around this ?

Thanks very much,
Regards, Jello



"Jello" wrote:

Thanks Rob thats great ! Many thanks

If I may ask one more question, would there have been any way I could have
found the answer to this via the help files in the VB editor ? I notice
intellisense works for some words but not all e.g. ActiveSheet.
Or should I buy a book or go to a good Vb help/tutorial web site ?

Thanks again,
Jello

"Rob Bovey" wrote:

"Jello" wrote in message
...
I have a workbook with many worksheets, each contains one or more charts.
ActiveSheet.ChartObjects("Chart 4").Activate
Once on an "ActiveChart" I can do:
debug.Print ActiveChart.Name

This is fine but I don't want to have to hard code "Chart4" in my
sub-procedure as on the 1st line - especially when its possible to access
it's name after I've originally told it it's name !

What I need to know is, how do retrieve properties such as chart names for
a
specified sheet ? Or for that matter any object which belongs to another
object - I see I it is possible to do a loop like For each cel in
range......
Is it possible to loop over items belonging to another object ?
I was wondering if for example there are "series" or "collections" of
charts
that a sheet may contain that can be accessed ?

Hi Jello,

Here's one way to look at all the ChartObjects on the ActiveSheet:

Sub EnumerateChartObjects()
Dim objChart As ChartObject
For Each objChart In ActiveSheet.ChartObjects
Debug.Print objChart.Name
Next objChart
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm