Hello,

I am getting a run-time 1004 error when trying to program properties for a
chart. Actually, it is the second chart which I create which is invoking
the error message.

I am creating multiple worksheets, each with one chart. The first worksheet
and chart work fine, but the second and third worksheet/chart cause 1004
errors on certain property values...

' create a new worksheet for chart flow
Application.DisplayAlerts = False
Set wsChartFlow = Worksheets.Add
wsChartFlow.Name = "Chart Flow"

' create the chart flow object
Set coFlow = wsChartFlow.ChartObjects.Add(0, 0, 800, 600)
coFlow.Name = "Net Flow over Time"
Set chFlow = coFlow.Chart
Application.DisplayAlerts = True

' add data to chart for Flow
chFlow.HasTitle = True
chFlow.ChartTitle.Caption = "Net Flow over Time"
chFlow.ChartType = xlXYScatterLines
chFlow.PlotBy = xlColumns

.... HEREIN I ADD SERIES AND GENERALLY FORMAT THE CHART TO MY LIKING ...
.... I ORIGINALLY TRIED ...

' create a new worksheet for chart fuel
Application.DisplayAlerts = False
Set wsChartFuel = Worksheets.Add
wsChartFuel.Name = "Chart Usage"

' create the chart fuel object
Set coFuel = wsChartFuel.ChartObjects.Add(0, 0, 5 * iCopy * m_iSelected,
600)
coFuel.Name = "Fuel Use over Time"
Set chFuel = coFuel.Chart
Application.DisplayAlerts = True

' add data to chart for Fuel
chFuel.HasTitle = True
chFuel.ChartTitle.Caption = "Fuel Use over Time"
chFuel.ChartType = xlColumnClustered
chFuel.PlotBy = xlColumns

.... IN THIS SEQUENCE, .HasTitle AND .ChartTitle.Caption GENERATE 1004'S, BUT
THE .ChartType AND .PlotBy PROPERTIES WORK JUST FINE.

I tried adding chFuel.Activate and received a 1004 for my trouble; so I
tried wsChartFuel.Activate (the sheet) and
wsChartFuel.ChartObjects(1).Activate (and the equivalent coFuel.Activate),
all of which work perfectly well, but still don't let me set any of the
problematic chart properties.

Being able to set some properties of the "chart" and getting a runtime error
when trying to set others is really most annoying. In fact, I cannot even
modify these properties in the debug "watches" window (I get 1004 there too).

Any help would be greatly appreciated.

Joseph A. J. Felcon

Are you sure you are doing

' add data to chart for Fuel
chFuel.HasTitle = True

If instead you are actually doing

'no series added yet
chFuel.HasTitle = True
' add data to chart for Fuel

would be one reason why doing HasTitle = true fails. IOW add at least one
series first.

In passing I don't follow how you are adding multiple sheets like this (all
with same name?)
> Set wsChartFuel = Worksheets.Add
> wsChartFuel.Name = "Chart Usage"

also can't think why you'd need to disable DisplayAlerts (unless deleting
sheets) though you may well want to disable screenupdating

Regards,
Peter T

"Joseph Felcon" <(E-Mail Removed)> wrote in message
news:746BF76D-22BB-4061-906A-(E-Mail Removed)...
> Hello,
>
> I am getting a run-time 1004 error when trying to program properties for a
> chart. Actually, it is the second chart which I create which is invoking
> the error message.
>
> I am creating multiple worksheets, each with one chart. The first
worksheet
> and chart work fine, but the second and third worksheet/chart cause 1004
> errors on certain property values...
>
> ' create a new worksheet for chart flow
> Application.DisplayAlerts = False
> Set wsChartFlow = Worksheets.Add
> wsChartFlow.Name = "Chart Flow"
>
> ' create the chart flow object
> Set coFlow = wsChartFlow.ChartObjects.Add(0, 0, 800, 600)
> coFlow.Name = "Net Flow over Time"
> Set chFlow = coFlow.Chart
> Application.DisplayAlerts = True
>
> ' add data to chart for Flow
> chFlow.HasTitle = True
> chFlow.ChartTitle.Caption = "Net Flow over Time"
> chFlow.ChartType = xlXYScatterLines
> chFlow.PlotBy = xlColumns
>
> ... HEREIN I ADD SERIES AND GENERALLY FORMAT THE CHART TO MY LIKING ...
> ... I ORIGINALLY TRIED ...
>
> ' create a new worksheet for chart fuel
> Application.DisplayAlerts = False
> Set wsChartFuel = Worksheets.Add
> wsChartFuel.Name = "Chart Usage"
>
> ' create the chart fuel object
> Set coFuel = wsChartFuel.ChartObjects.Add(0, 0, 5 * iCopy * m_iSelected,
> 600)
> coFuel.Name = "Fuel Use over Time"
> Set chFuel = coFuel.Chart
> Application.DisplayAlerts = True
>
> ' add data to chart for Fuel
> chFuel.HasTitle = True
> chFuel.ChartTitle.Caption = "Fuel Use over Time"
> chFuel.ChartType = xlColumnClustered
> chFuel.PlotBy = xlColumns
>
> ... IN THIS SEQUENCE, .HasTitle AND .ChartTitle.Caption GENERATE 1004'S,
BUT
> THE .ChartType AND .PlotBy PROPERTIES WORK JUST FINE.
>
> I tried adding chFuel.Activate and received a 1004 for my trouble; so I
> tried wsChartFuel.Activate (the sheet) and
> wsChartFuel.ChartObjects(1).Activate (and the equivalent coFuel.Activate),
> all of which work perfectly well, but still don't let me set any of the
> problematic chart properties.
>
> Being able to set some properties of the "chart" and getting a runtime
error
> when trying to set others is really most annoying. In fact, I cannot even
> modify these properties in the debug "watches" window (I get 1004 there
too).
>
> Any help would be greatly appreciated.
>
> Joseph A. J. Felcon

Sir,

Thanks for the information. I was unaware that one had to put data into a
chart before being able to add a title to it. I have tried it and it works,
but I don't like it.

This is a hidden "feature" (so typical of our friends at MS), because it is
nowhere stated in the documentation. It also violates several principles of
object oriented design to tie together unrelated properties in this way,
especially when the owned objects, themselves, have a property of the same
name. It may be right, but it is definitely not proper. It is also both
arbitrary and counterintuitive, and I am curious as to how you would know
this.

As for your comments about the code, I think that you misread. The
worksheet objects have distinct names (one is Fuel and one is Flow). Turning
off Alerts relieves my customers from seeing a lot of messages about internal
ops and having to click OK a few hundred times, while seeing screen updates
under the driver form is a positive, because it gives them a sense of
activity (i.e.: that something is actually happening while they wait).

Thanks again for information. It works and I will keep it in mind while
working with this, or similar, Excel objects, but I still think that it is
improper.

Joseph A. J. Felcon

"Peter T" wrote:

> Are you sure you are doing
>
> ' add data to chart for Fuel
> chFuel.HasTitle = True
>
> If instead you are actually doing
>
> 'no series added yet
> chFuel.HasTitle = True
> ' add data to chart for Fuel
>
> would be one reason why doing HasTitle = true fails. IOW add at least one
> series first.
>
> In passing I don't follow how you are adding multiple sheets like this (all
> with same name?)
> > Set wsChartFuel = Worksheets.Add
> > wsChartFuel.Name = "Chart Usage"
>
> also can't think why you'd need to disable DisplayAlerts (unless deleting
> sheets) though you may well want to disable screenupdating
>
> Regards,
> Peter T
>
> "Joseph Felcon" <(E-Mail Removed)> wrote in message
> news:746BF76D-22BB-4061-906A-(E-Mail Removed)...
> > Hello,
> >
> > I am getting a run-time 1004 error when trying to program properties for a
> > chart. Actually, it is the second chart which I create which is invoking
> > the error message.
> >
> > I am creating multiple worksheets, each with one chart. The first
> worksheet
> > and chart work fine, but the second and third worksheet/chart cause 1004
> > errors on certain property values...
> >
> > ' create a new worksheet for chart flow
> > Application.DisplayAlerts = False
> > Set wsChartFlow = Worksheets.Add
> > wsChartFlow.Name = "Chart Flow"
> >
> > ' create the chart flow object
> > Set coFlow = wsChartFlow.ChartObjects.Add(0, 0, 800, 600)
> > coFlow.Name = "Net Flow over Time"
> > Set chFlow = coFlow.Chart
> > Application.DisplayAlerts = True
> >
> > ' add data to chart for Flow
> > chFlow.HasTitle = True
> > chFlow.ChartTitle.Caption = "Net Flow over Time"
> > chFlow.ChartType = xlXYScatterLines
> > chFlow.PlotBy = xlColumns
> >
> > ... HEREIN I ADD SERIES AND GENERALLY FORMAT THE CHART TO MY LIKING ...
> > ... I ORIGINALLY TRIED ...
> >
> > ' create a new worksheet for chart fuel
> > Application.DisplayAlerts = False
> > Set wsChartFuel = Worksheets.Add
> > wsChartFuel.Name = "Chart Usage"
> >
> > ' create the chart fuel object
> > Set coFuel = wsChartFuel.ChartObjects.Add(0, 0, 5 * iCopy * m_iSelected,
> > 600)
> > coFuel.Name = "Fuel Use over Time"
> > Set chFuel = coFuel.Chart
> > Application.DisplayAlerts = True
> >
> > ' add data to chart for Fuel
> > chFuel.HasTitle = True
> > chFuel.ChartTitle.Caption = "Fuel Use over Time"
> > chFuel.ChartType = xlColumnClustered
> > chFuel.PlotBy = xlColumns
> >
> > ... IN THIS SEQUENCE, .HasTitle AND .ChartTitle.Caption GENERATE 1004'S,
> BUT
> > THE .ChartType AND .PlotBy PROPERTIES WORK JUST FINE.
> >
> > I tried adding chFuel.Activate and received a 1004 for my trouble; so I
> > tried wsChartFuel.Activate (the sheet) and
> > wsChartFuel.ChartObjects(1).Activate (and the equivalent coFuel.Activate),
> > all of which work perfectly well, but still don't let me set any of the
> > problematic chart properties.
> >
> > Being able to set some properties of the "chart" and getting a runtime
> error
> > when trying to set others is really most annoying. In fact, I cannot even
> > modify these properties in the debug "watches" window (I get 1004 there
> too).
> >
> > Any help would be greatly appreciated.
> >
> > Joseph A. J. Felcon
>
>
>
>

> It may be right, but it is definitely not proper.

I like it!
I'll file it with "The customer is always right but the customer is not
always correct"

> It is also both arbitrary and counterintuitive, and I am curious
> as to how you would know this.

There are many things about the Excel chart object and how it is exposed to
VBA which are, for want of a better word, odd. The one you have picked up on
I'd describe as no more than a quirk.

Regards,
Peter T

"Joseph Felcon" <(E-Mail Removed)> wrote in message
news:393450C4-C435-42A1-AD13-(E-Mail Removed)...
> Sir,
>
> Thanks for the information. I was unaware that one had to put data into a
> chart before being able to add a title to it. I have tried it and it
works,
> but I don't like it.
>
> This is a hidden "feature" (so typical of our friends at MS), because it
is
> nowhere stated in the documentation. It also violates several principles
of
> object oriented design to tie together unrelated properties in this way,
> especially when the owned objects, themselves, have a property of the same
> name. It may be right, but it is definitely not proper. It is also both
> arbitrary and counterintuitive, and I am curious as to how you would know
> this.
>
> As for your comments about the code, I think that you misread. The
> worksheet objects have distinct names (one is Fuel and one is Flow).
Turning
> off Alerts relieves my customers from seeing a lot of messages about
internal
> ops and having to click OK a few hundred times, while seeing screen
updates
> under the driver form is a positive, because it gives them a sense of
> activity (i.e.: that something is actually happening while they wait).
>
> Thanks again for information. It works and I will keep it in mind while
> working with this, or similar, Excel objects, but I still think that it is
> improper.
>
> Joseph A. J. Felcon
>
> "Peter T" wrote:
>
> > Are you sure you are doing
> >
> > ' add data to chart for Fuel
> > chFuel.HasTitle = True
> >
> > If instead you are actually doing
> >
> > 'no series added yet
> > chFuel.HasTitle = True
> > ' add data to chart for Fuel
> >
> > would be one reason why doing HasTitle = true fails. IOW add at least
one
> > series first.
> >
> > In passing I don't follow how you are adding multiple sheets like this
(all
> > with same name?)
> > > Set wsChartFuel = Worksheets.Add
> > > wsChartFuel.Name = "Chart Usage"
> >
> > also can't think why you'd need to disable DisplayAlerts (unless
deleting
> > sheets) though you may well want to disable screenupdating
> >
> > Regards,
> > Peter T
> >
> > "Joseph Felcon" <(E-Mail Removed)> wrote in
message
> > news:746BF76D-22BB-4061-906A-(E-Mail Removed)...
> > > Hello,
> > >
> > > I am getting a run-time 1004 error when trying to program properties
for a
> > > chart. Actually, it is the second chart which I create which is
invoking
> > > the error message.
> > >
> > > I am creating multiple worksheets, each with one chart. The first
> > worksheet
> > > and chart work fine, but the second and third worksheet/chart cause
1004
> > > errors on certain property values...
> > >
> > > ' create a new worksheet for chart flow
> > > Application.DisplayAlerts = False
> > > Set wsChartFlow = Worksheets.Add
> > > wsChartFlow.Name = "Chart Flow"
> > >
> > > ' create the chart flow object
> > > Set coFlow = wsChartFlow.ChartObjects.Add(0, 0, 800, 600)
> > > coFlow.Name = "Net Flow over Time"
> > > Set chFlow = coFlow.Chart
> > > Application.DisplayAlerts = True
> > >
> > > ' add data to chart for Flow
> > > chFlow.HasTitle = True
> > > chFlow.ChartTitle.Caption = "Net Flow over Time"
> > > chFlow.ChartType = xlXYScatterLines
> > > chFlow.PlotBy = xlColumns
> > >
> > > ... HEREIN I ADD SERIES AND GENERALLY FORMAT THE CHART TO MY LIKING
....
> > > ... I ORIGINALLY TRIED ...
> > >
> > > ' create a new worksheet for chart fuel
> > > Application.DisplayAlerts = False
> > > Set wsChartFuel = Worksheets.Add
> > > wsChartFuel.Name = "Chart Usage"
> > >
> > > ' create the chart fuel object
> > > Set coFuel = wsChartFuel.ChartObjects.Add(0, 0, 5 * iCopy *
m_iSelected,
> > > 600)
> > > coFuel.Name = "Fuel Use over Time"
> > > Set chFuel = coFuel.Chart
> > > Application.DisplayAlerts = True
> > >
> > > ' add data to chart for Fuel
> > > chFuel.HasTitle = True
> > > chFuel.ChartTitle.Caption = "Fuel Use over Time"
> > > chFuel.ChartType = xlColumnClustered
> > > chFuel.PlotBy = xlColumns
> > >
> > > ... IN THIS SEQUENCE, .HasTitle AND .ChartTitle.Caption GENERATE
1004'S,
> > BUT
> > > THE .ChartType AND .PlotBy PROPERTIES WORK JUST FINE.
> > >
> > > I tried adding chFuel.Activate and received a 1004 for my trouble; so
I
> > > tried wsChartFuel.Activate (the sheet) and
> > > wsChartFuel.ChartObjects(1).Activate (and the equivalent
coFuel.Activate),
> > > all of which work perfectly well, but still don't let me set any of
the
> > > problematic chart properties.
> > >
> > > Being able to set some properties of the "chart" and getting a runtime
> > error
> > > when trying to set others is really most annoying. In fact, I cannot
even
> > > modify these properties in the debug "watches" window (I get 1004
there
> > too).
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Joseph A. J. Felcon
> >
> >
> >
> >

Sir,

Odd, indeed! And it starts with functionality shared between a "Chart
Object" object and a "Chart" object, which is semantically confusing to say
the least.

And I don't mind the documented oddities; what makes me a little nuts are
those undocumented quirks that one is just supposed to intuit. They can make
a poor developer into a drooling idiot (or at least, to be so perceived by
his/her manager, which may have a very detrimental effect on one's career
path).

Joseph A. J. Felcon

"Peter T" wrote:

> > It may be right, but it is definitely not proper.
>
> I like it!
> I'll file it with "The customer is always right but the customer is not
> always correct"
>
> > It is also both arbitrary and counterintuitive, and I am curious
> > as to how you would know this.
>
> There are many things about the Excel chart object and how it is exposed to
> VBA which are, for want of a better word, odd. The one you have picked up on
> I'd describe as no more than a quirk.
>
> Regards,
> Peter T
>
> "Joseph Felcon" <(E-Mail Removed)> wrote in message
> news:393450C4-C435-42A1-AD13-(E-Mail Removed)...
> > Sir,
> >
> > Thanks for the information. I was unaware that one had to put data into a
> > chart before being able to add a title to it. I have tried it and it
> works,
> > but I don't like it.
> >
> > This is a hidden "feature" (so typical of our friends at MS), because it
> is
> > nowhere stated in the documentation. It also violates several principles
> of
> > object oriented design to tie together unrelated properties in this way,
> > especially when the owned objects, themselves, have a property of the same
> > name. It may be right, but it is definitely not proper. It is also both
> > arbitrary and counterintuitive, and I am curious as to how you would know
> > this.
> >
> > As for your comments about the code, I think that you misread. The
> > worksheet objects have distinct names (one is Fuel and one is Flow).
> Turning
> > off Alerts relieves my customers from seeing a lot of messages about
> internal
> > ops and having to click OK a few hundred times, while seeing screen
> updates
> > under the driver form is a positive, because it gives them a sense of
> > activity (i.e.: that something is actually happening while they wait).
> >
> > Thanks again for information. It works and I will keep it in mind while
> > working with this, or similar, Excel objects, but I still think that it is
> > improper.
> >
> > Joseph A. J. Felcon
> >
> > "Peter T" wrote:
> >
> > > Are you sure you are doing
> > >
> > > ' add data to chart for Fuel
> > > chFuel.HasTitle = True
> > >
> > > If instead you are actually doing
> > >
> > > 'no series added yet
> > > chFuel.HasTitle = True
> > > ' add data to chart for Fuel
> > >
> > > would be one reason why doing HasTitle = true fails. IOW add at least
> one
> > > series first.
> > >
> > > In passing I don't follow how you are adding multiple sheets like this
> (all
> > > with same name?)
> > > > Set wsChartFuel = Worksheets.Add
> > > > wsChartFuel.Name = "Chart Usage"
> > >
> > > also can't think why you'd need to disable DisplayAlerts (unless
> deleting
> > > sheets) though you may well want to disable screenupdating
> > >
> > > Regards,
> > > Peter T
> > >
> > > "Joseph Felcon" <(E-Mail Removed)> wrote in
> message
> > > news:746BF76D-22BB-4061-906A-(E-Mail Removed)...
> > > > Hello,
> > > >
> > > > I am getting a run-time 1004 error when trying to program properties
> for a
> > > > chart. Actually, it is the second chart which I create which is
> invoking
> > > > the error message.
> > > >
> > > > I am creating multiple worksheets, each with one chart. The first
> > > worksheet
> > > > and chart work fine, but the second and third worksheet/chart cause
> 1004
> > > > errors on certain property values...
> > > >
> > > > ' create a new worksheet for chart flow
> > > > Application.DisplayAlerts = False
> > > > Set wsChartFlow = Worksheets.Add
> > > > wsChartFlow.Name = "Chart Flow"
> > > >
> > > > ' create the chart flow object
> > > > Set coFlow = wsChartFlow.ChartObjects.Add(0, 0, 800, 600)
> > > > coFlow.Name = "Net Flow over Time"
> > > > Set chFlow = coFlow.Chart
> > > > Application.DisplayAlerts = True
> > > >
> > > > ' add data to chart for Flow
> > > > chFlow.HasTitle = True
> > > > chFlow.ChartTitle.Caption = "Net Flow over Time"
> > > > chFlow.ChartType = xlXYScatterLines
> > > > chFlow.PlotBy = xlColumns
> > > >
> > > > ... HEREIN I ADD SERIES AND GENERALLY FORMAT THE CHART TO MY LIKING
> ....
> > > > ... I ORIGINALLY TRIED ...
> > > >
> > > > ' create a new worksheet for chart fuel
> > > > Application.DisplayAlerts = False
> > > > Set wsChartFuel = Worksheets.Add
> > > > wsChartFuel.Name = "Chart Usage"
> > > >
> > > > ' create the chart fuel object
> > > > Set coFuel = wsChartFuel.ChartObjects.Add(0, 0, 5 * iCopy *
> m_iSelected,
> > > > 600)
> > > > coFuel.Name = "Fuel Use over Time"
> > > > Set chFuel = coFuel.Chart
> > > > Application.DisplayAlerts = True
> > > >
> > > > ' add data to chart for Fuel
> > > > chFuel.HasTitle = True
> > > > chFuel.ChartTitle.Caption = "Fuel Use over Time"
> > > > chFuel.ChartType = xlColumnClustered
> > > > chFuel.PlotBy = xlColumns
> > > >
> > > > ... IN THIS SEQUENCE, .HasTitle AND .ChartTitle.Caption GENERATE
> 1004'S,
> > > BUT
> > > > THE .ChartType AND .PlotBy PROPERTIES WORK JUST FINE.
> > > >
> > > > I tried adding chFuel.Activate and received a 1004 for my trouble; so
> I
> > > > tried wsChartFuel.Activate (the sheet) and
> > > > wsChartFuel.ChartObjects(1).Activate (and the equivalent
> coFuel.Activate),
> > > > all of which work perfectly well, but still don't let me set any of
> the
> > > > problematic chart properties.
> > > >
> > > > Being able to set some properties of the "chart" and getting a runtime
> > > error
> > > > when trying to set others is really most annoying. In fact, I cannot
> even
> > > > modify these properties in the debug "watches" window (I get 1004
> there
> > > too).
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Joseph A. J. Felcon
> > >
> > >
> > >
> > >
>
>
>