Hello,

What methods are there to determine properties of a sheet or any object ?

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 ?

Thanks very much indeed,
Have a good day.

"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

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

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

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

Hi Jello,

Although a good book certainly won't hurt, one of the best ways to
figure out the Excel object model is by using the Object Browser. Press F2
in the VB Editor to bring it up. The ActiveSheet is typically a worksheet,
so if you scroll down the Object Browser classes list on the left side and
select the Worksheet object you'll see all the properties, methods and
events associated with a Worksheet on the right side. One of those methods
is the ChartObjects method, which you can use either to return a specific
ChartObject reference as in your original post, or loop to return a list of
all ChartObjects on the Worksheet as I showed.

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

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

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

Hi Jello,

To test the function in the Immediate window using your example you
would do this:

? getChartName(Worksheets("Results"))

However, this function really doesn't make sense because it will behave
incorrectly if you ever have more than one ChartObject on a worksheet.
Instead, use the other syntax I showed:

? Worksheets("Results").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
...
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