I am using Excel 2003. I'm using "Option Explicit" to force variable
declaration. I want to tie the axes of a chart that I have named "MyChart"
to worksheet cells. I've been working with the following but continue to get
errors:

Dim Cht as ChartObject

Set Cht = Worksheets("Model").ChartObjects("MyChart").Chart

With Cht.Axes(xlValue)
.MaximumScale = Sheets("Charts").Range("C7")
.MinimumScale = Sheets("Charts").Range("C9")
.MajorUnit = Sheets("Charts").Range("C8")
End With

If I take out the "Option Explicit" I can get things to work. However, I
would like to keep it in my code to help make sure I am declaring variables
correctly.

Can anyone advise as to what I am doing wrong? Thank you in advance for
your help.

--
Steph

Steph a écrit :

Hi

To me, Cht is a Chart, not a ChartObject

so...

Dim Cht As Chart


--
François L


> I am using Excel 2003. I'm using "Option Explicit" to force variable
> declaration. I want to tie the axes of a chart that I have named "MyChart"
> to worksheet cells. I've been working with the following but continue to get
> errors:
>
> Dim Cht as ChartObject
>
> Set Cht = Worksheets("Model").ChartObjects("MyChart").Chart
>
> With Cht.Axes(xlValue)
> .MaximumScale = Sheets("Charts").Range("C7")
> .MinimumScale = Sheets("Charts").Range("C9")
> .MajorUnit = Sheets("Charts").Range("C8")
> End With
>
> If I take out the "Option Explicit" I can get things to work. However, I
> would like to keep it in my code to help make sure I am declaring variables
> correctly.
>
> Can anyone advise as to what I am doing wrong? Thank you in advance for
> your help.
>

Hi,

You have declared the object variable Cht as ChartObject but when
setting the reference to it you actually drill further down the object
model and reference the Chart.

Either change the declaration to,

Dim Cht As Chart

Or the way you set the reference and then use it,

Set Cht = Worksheets("Model").ChartObjects("MyChart")

With Cht.Chart.Axes(xlValue)

Cheers
Andy

Steph wrote:
> I am using Excel 2003. I'm using "Option Explicit" to force variable
> declaration. I want to tie the axes of a chart that I have named "MyChart"
> to worksheet cells. I've been working with the following but continue to get
> errors:
>
> Dim Cht as ChartObject
>
> Set Cht = Worksheets("Model").ChartObjects("MyChart").Chart
>
> With Cht.Axes(xlValue)
> .MaximumScale = Sheets("Charts").Range("C7")
> .MinimumScale = Sheets("Charts").Range("C9")
> .MajorUnit = Sheets("Charts").Range("C8")
> End With
>
> If I take out the "Option Explicit" I can get things to work. However, I
> would like to keep it in my code to help make sure I am declaring variables
> correctly.
>
> Can anyone advise as to what I am doing wrong? Thank you in advance for
> your help.
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info