Dear Rob,

I am having difficulty understanding how to test this function.
When I run the funtion in the immediate window with:

?getChartName("Results") I get compile error: type mismatch.

I guess the "Results" bit is probably wrong but what should it be ?

Am I running this funtion wrong ? The name of my sheet is "Results".

When you say "I'm making the simplifying assumption that you won't be
putting ChartObjects on Chart sheets", I'm not sure I understand what a chart sheet is but I basically just have a chart on a worksheet(that is as I see it a tab down at the bottom with a chart created on the sheet - my file(is that called a workbook ?!) is called Stats.xls

Sorry for the trouble... And my lack of knowledge :)

Kind Regards & many thanks
Jello

"Rob Bovey" wrote:

Hi Jello,

I'm not entirely sure what you want to do here. The following
modification of your function allows you to pass a reference to the
worksheet itself (I'm making the simplifying assumption that you won't be
putting ChartObjects on Chart sheets):

Function getChartName(wksSheet As Worksheet) As String
Dim objChart As ChartObject
For Each objChart In wksSheet.ChartObjects
getChartName = objChart.Name
Next objChart
End Function

Sub Demo()
Debug.Print getChartName(ActiveSheet)
End Sub

However, if the specified worksheet contains more than one ChartObject
you will only get the name of the last one, and if the worksheet will never
contain more than one ChartObject it would be a lot easier to do this:

Dim szChartName As String
szChartName = ActiveSheet.ChartObjects(1).Name

--
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

"Jello" wrote in message
...
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