help new to vba - charts??
 

I have a chart on a work sheet, and the sheet is called "Operator
Input" and the charts name is "rate"

My question is - How do i select the chart?

i have tried this

ActiveSheet.ChartObjects("rate").Select

and i get an error

but this seems to work

ActiveSheet.ChartObjects("rate").Activate

is the Activate the only way to do the selection??


Thanks in advance

Vickie

help new to vba - charts??
 

Vickie,

I believe both should work.
However, you will be much happier by not trying to select the chart,
but by setting an object reference to the chart and using the object
reference to do things.

Also, the syntax for a chart on a Worksheet and a separate Chart Sheet are different.
A chart on a worksheet in contained within a ChartObject. A chart sheet is a chart.
If that doesn't make sense, then I believe that was Microsoft's intent.<g

Take a look at the following example, that changes the background color
of a chart on a worksheet. It should work on the first chart shown on
the active worksheet (including your "rate" chart).
(Be aware that there is a separate newsgroup devoted only to charts...
"microsoft.public.excel.charting")
'----------------------------------
Sub Test()
Dim chtRate As Excel.Chart
Dim lngColor As Long

'Make an object reference to the chart within the ChartObject
Set chtRate = ActiveSheet.ChartObjects(1).Chart

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex

'Change the chartarea color
chtRate.ChartArea.Interior.ColorIndex = 40

MsgBox "Chart color changed "

'Return the chartarea color back to original color.
chtRate.ChartArea.Interior.ColorIndex = lngColor

Set chtRate = Nothing
End Sub
'----------------------------------

Regards,
Jim Cone
San Francisco, USA


"vickie_raven" wrote in message
...
I have a chart on a work sheet, and the sheet is called "Operator
Input" and the charts name is "rate"
My question is - How do i select the chart?
i have tried this
ActiveSheet.ChartObjects("rate").Select
and i get an error
but this seems to work
ActiveSheet.ChartObjects("rate").Activate
is the Activate the only way to do the selection??
Thanks in advance
Vickie

help new to vba - charts??
 

Jim

Thank you so much for taking the time to help me!!!
I am working with your example now.

Thank you again!!

Vickie





On Sun, 27 Feb 2005 16:47:27 -0800, "Jim Cone"
wrote:

Vickie,

I believe both should work.
However, you will be much happier by not trying to select the chart,
but by setting an object reference to the chart and using the object
reference to do things.

Also, the syntax for a chart on a Worksheet and a separate Chart Sheet are different.
A chart on a worksheet in contained within a ChartObject. A chart sheet is a chart.
If that doesn't make sense, then I believe that was Microsoft's intent.<g

Take a look at the following example, that changes the background color
of a chart on a worksheet. It should work on the first chart shown on
the active worksheet (including your "rate" chart).
(Be aware that there is a separate newsgroup devoted only to charts...
"microsoft.public.excel.charting")
'----------------------------------
Sub Test()
Dim chtRate As Excel.Chart
Dim lngColor As Long

'Make an object reference to the chart within the ChartObject
Set chtRate = ActiveSheet.ChartObjects(1).Chart

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex

'Change the chartarea color
chtRate.ChartArea.Interior.ColorIndex = 40

MsgBox "Chart color changed "

'Return the chartarea color back to original color.
chtRate.ChartArea.Interior.ColorIndex = lngColor

Set chtRate = Nothing
End Sub
'----------------------------------

Regards,
Jim Cone
San Francisco, USA


"vickie_raven" wrote in message
.. .
I have a chart on a work sheet, and the sheet is called "Operator
Input" and the charts name is "rate"
My question is - How do i select the chart?
i have tried this
ActiveSheet.ChartObjects("rate").Select
and i get an error
but this seems to work
ActiveSheet.ChartObjects("rate").Activate
is the Activate the only way to do the selection??
Thanks in advance
Vickie

help new to vba - charts??
 

When trying the suggested chart area dynamic colorization with a function
invoked from a cell withing the chart's sheet, the code seems to work, yet
the color reverts to its original. Even manually modifying the chartarea
color to 40 in debug is ignored and reverts to the original 15. Any clues
would be appreciated.

Function Test()
Dim chtRate As Excel.Chart
Dim lngColor As Long

'Make an object reference to the chart within the ChartObject
Set chtRate = ActiveSheet.ChartObjects(1).Chart

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex (works lngColor = 15)

lngColor = 40 (works lngColor = 40)

'Change the chartarea color
chtRate.ChartArea.Interior.ColorIndex = lngColor (doesn't work lngColor
remains 15)
'chtRate.ChartArea.Interior.ColorIndex = 40

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex (after execution, both=15)


'Return the chartarea color back to original color.
'chtRate.ChartArea.Interior.ColorIndex = lngColor

'Set chtRate = Nothing
End Function

"Jim Cone" wrote:

Vickie,

I believe both should work.
However, you will be much happier by not trying to select the chart,
but by setting an object reference to the chart and using the object
reference to do things.

Also, the syntax for a chart on a Worksheet and a separate Chart Sheet are different.
A chart on a worksheet in contained within a ChartObject. A chart sheet is a chart.
If that doesn't make sense, then I believe that was Microsoft's intent.<g

Take a look at the following example, that changes the background color
of a chart on a worksheet. It should work on the first chart shown on
the active worksheet (including your "rate" chart).
(Be aware that there is a separate newsgroup devoted only to charts...
"microsoft.public.excel.charting")
'----------------------------------
Sub Test()
Dim chtRate As Excel.Chart
Dim lngColor As Long

'Make an object reference to the chart within the ChartObject
Set chtRate = ActiveSheet.ChartObjects(1).Chart

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex

'Change the chartarea color
chtRate.ChartArea.Interior.ColorIndex = 40

MsgBox "Chart color changed "

'Return the chartarea color back to original color.
chtRate.ChartArea.Interior.ColorIndex = lngColor

Set chtRate = Nothing
End Sub
'----------------------------------

Regards,
Jim Cone
San Francisco, USA


"vickie_raven" wrote in message
...
I have a chart on a work sheet, and the sheet is called "Operator
Input" and the charts name is "rate"
My question is - How do i select the chart?
i have tried this
ActiveSheet.ChartObjects("rate").Select
and i get an error
but this seems to work
ActiveSheet.ChartObjects("rate").Activate
is the Activate the only way to do the selection??
Thanks in advance
Vickie

help new to vba - charts??
 

Correction from previous post: chtRate.ChartArea.Interior.ColorIndex =
lngColor (strike this: doesn't work lngColor remains 15) (replacement
correction: chtRate.ChartArea.Interior.ColorIndex remains 15)


"silver23" wrote:

When trying the suggested chart area dynamic colorization with a function
invoked from a cell withing the chart's sheet, the code seems to work, yet
the color reverts to its original. Even manually modifying the chartarea
color to 40 in debug is ignored and reverts to the original 15. Any clues
would be appreciated.

Function Test()
Dim chtRate As Excel.Chart
Dim lngColor As Long

'Make an object reference to the chart within the ChartObject
Set chtRate = ActiveSheet.ChartObjects(1).Chart

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex (works lngColor = 15)

lngColor = 40 (works lngColor = 40)

'Change the chartarea color
chtRate.ChartArea.Interior.ColorIndex = lngColor (strike this: doesn't work lngColor remains 15) (replacement correction: chtRate.ChartArea.Interior.ColorIndex remains 15)
'chtRate.ChartArea.Interior.ColorIndex = 40

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex (after execution, both=15)


'Return the chartarea color back to original color.
'chtRate.ChartArea.Interior.ColorIndex = lngColor

'Set chtRate = Nothing
End Function

"Jim Cone" wrote:

Vickie,

I believe both should work.
However, you will be much happier by not trying to select the chart,
but by setting an object reference to the chart and using the object
reference to do things.

Also, the syntax for a chart on a Worksheet and a separate Chart Sheet are different.
A chart on a worksheet in contained within a ChartObject. A chart sheet is a chart.
If that doesn't make sense, then I believe that was Microsoft's intent.<g

Take a look at the following example, that changes the background color
of a chart on a worksheet. It should work on the first chart shown on
the active worksheet (including your "rate" chart).
(Be aware that there is a separate newsgroup devoted only to charts...
"microsoft.public.excel.charting")
'----------------------------------
Sub Test()
Dim chtRate As Excel.Chart
Dim lngColor As Long

'Make an object reference to the chart within the ChartObject
Set chtRate = ActiveSheet.ChartObjects(1).Chart

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex

'Change the chartarea color
chtRate.ChartArea.Interior.ColorIndex = 40

MsgBox "Chart color changed "

'Return the chartarea color back to original color.
chtRate.ChartArea.Interior.ColorIndex = lngColor

Set chtRate = Nothing
End Sub
'----------------------------------

Regards,
Jim Cone
San Francisco, USA


"vickie_raven" wrote in message
...
I have a chart on a work sheet, and the sheet is called "Operator
Input" and the charts name is "rate"
My question is - How do i select the chart?
i have tried this
ActiveSheet.ChartObjects("rate").Select
and i get an error
but this seems to work
ActiveSheet.ChartObjects("rate").Activate
is the Activate the only way to do the selection??
Thanks in advance
Vickie

help new to vba - charts??
 

In Subject: background-foreground color 5/26/2005 8:31 AM PST
By: silver23 In: microsoft.public.excel.charting

Jim Cone is deserving of credit for offering the basis of this solution to
chartarea colorization.

"Jim Cone" wrote:

Vickie,

I believe both should work.
However, you will be much happier by not trying to select the chart,
but by setting an object reference to the chart and using the object
reference to do things.

Also, the syntax for a chart on a Worksheet and a separate Chart Sheet are different.
A chart on a worksheet in contained within a ChartObject. A chart sheet is a chart.
If that doesn't make sense, then I believe that was Microsoft's intent.<g

Take a look at the following example, that changes the background color
of a chart on a worksheet. It should work on the first chart shown on
the active worksheet (including your "rate" chart).
(Be aware that there is a separate newsgroup devoted only to charts...
"microsoft.public.excel.charting")
'----------------------------------
Sub Test()
Dim chtRate As Excel.Chart
Dim lngColor As Long

'Make an object reference to the chart within the ChartObject
Set chtRate = ActiveSheet.ChartObjects(1).Chart

'Use the reference to determine the Chartarea color
lngColor = chtRate.ChartArea.Interior.ColorIndex

'Change the chartarea color
chtRate.ChartArea.Interior.ColorIndex = 40

MsgBox "Chart color changed "

'Return the chartarea color back to original color.
chtRate.ChartArea.Interior.ColorIndex = lngColor

Set chtRate = Nothing
End Sub
'----------------------------------

Regards,
Jim Cone
San Francisco, USA


"vickie_raven" wrote in message
...
I have a chart on a work sheet, and the sheet is called "Operator
Input" and the charts name is "rate"
My question is - How do i select the chart?
i have tried this
ActiveSheet.ChartObjects("rate").Select
and i get an error
but this seems to work
ActiveSheet.ChartObjects("rate").Activate
is the Activate the only way to do the selection??
Thanks in advance
Vickie