Hi all,
in my macro I used the

Chart.Add instruction.

Every thing I want to apply to new chart must be preceded by an Activate call.

How I must do this?
Otherwise I receive always the same Error 1004.

Thanks a lot

A lex.

Alex,

I'm not sure what you are asking, but if you use the macro recorder while creating a chart you get something like the following.

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("C26:C30")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"

Is this the style of code that is causing your error?

--

John Green - Excel MVP
Sydney
Australia


"y" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hi all,
> in my macro I used the
>
> Chart.Add instruction.
>
> Every thing I want to apply to new chart must be preceded by an Activate call.
>
> How I must do this?
> Otherwise I receive always the same Error 1004.
>
> Thanks a lot
>
> A lex.
>

Hi John and thank you for your answer,

I posted my question because I used the macro recorder to initialize the macro, but I'm not able to
run it again!

I serched via google "xlSurface group:*Excel*" and it seems to me that Surface2D chart are hard to
manage.

I want an instruction to by-pass the error 1004 that VBA returns to me whan it runs the following
lines of code:

Sub Chart2Dsurface()

Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer

Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Nseries = sArea.Rows.Count - 1
Charts.Add.Name = "pippo"

>> Charts("pippo").ChartType = xlSurface << Error 1004 !!!

Application.WorksheetFunction.Chart.Activate

Thanks Alex.

John Green wrote:

> Alex,
>
> I'm not sure what you are asking, but if you use the macro recorder while creating a chart you get something like the following.
>
> Charts.Add
> ActiveChart.ChartType = xlColumnClustered
> ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("C26:C30")
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
>
> Is this the style of code that is causing your error?
>

Alex,

If your aim is to store a reference to the new chart I suggest creating an object variable.


Set myChart = Charts.Add

With myChart
.Name = "Pippo"
.ChartType = xlSurface
End With

Are you creating a chart sheet or an embedded chart in a worksheet? There are differences in the code required.

--

John Green - Excel MVP
Sydney
Australia


"y" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hi John and thank you for your answer,
>
> I posted my question because I used the macro recorder to initialize the macro, but I'm not able to
> run it again!
>
> I serched via google "xlSurface group:*Excel*" and it seems to me that Surface2D chart are hard to
> manage.
>
> I want an instruction to by-pass the error 1004 that VBA returns to me whan it runs the following
> lines of code:
>
> Sub Chart2Dsurface()
>
> Dim sArea As Range
> Dim i As Integer
> Dim Nseries As Integer
>
> Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
> Nseries = sArea.Rows.Count - 1
> Charts.Add.Name = "pippo"
>
> >> Charts("pippo").ChartType = xlSurface << Error 1004 !!!
>
> Application.WorksheetFunction.Chart.Activate
>
> Thanks Alex.
>
> John Green wrote:
>
> > Alex,
> >
> > I'm not sure what you are asking, but if you use the macro recorder while creating a chart you get something like the following.
> >
> > Charts.Add
> > ActiveChart.ChartType = xlColumnClustered
> > ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("C26:C30")
> > ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
> >
> > Is this the style of code that is causing your error?
> >
>

The following code works for me under Excel 2003

Sub Chart2Dsurface()

Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer
Dim myChart As Chart

Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Nseries = sArea.Rows.Count - 1

Set myChart = Charts.Add
With myChart
.SetSourceData Source:=sArea
.Name = "Pippo"
.ChartType = xlSurface
.Location where:=xlLocationAsObject, Name:="Sheet2"
End With

End Sub


--

John Green - Excel MVP
Sydney
Australia


"y" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hi John and thank you for your answer,
>
> I posted my question because I used the macro recorder to initialize the macro, but I'm not able to
> run it again!
>
> I serched via google "xlSurface group:*Excel*" and it seems to me that Surface2D chart are hard to
> manage.
>
> I want an instruction to by-pass the error 1004 that VBA returns to me whan it runs the following
> lines of code:
>
> Sub Chart2Dsurface()
>
> Dim sArea As Range
> Dim i As Integer
> Dim Nseries As Integer
>
> Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
> Nseries = sArea.Rows.Count - 1
> Charts.Add.Name = "pippo"
>
> >> Charts("pippo").ChartType = xlSurface << Error 1004 !!!
>
> Application.WorksheetFunction.Chart.Activate
>
> Thanks Alex.
>
> John Green wrote:
>
> > Alex,
> >
> > I'm not sure what you are asking, but if you use the macro recorder while creating a chart you get something like the following.
> >
> > Charts.Add
> > ActiveChart.ChartType = xlColumnClustered
> > ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("C26:C30")
> > ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
> >
> > Is this the style of code that is causing your error?
> >
>

Thank you John,
I'll try your suggestions.

First of all I bypassed the problem postponing the the instruction with the xlSurface to the bottom
of my macro. It works.

Second, I would ask you how to convert a range reference to a string within a macro.
The instruction to set the data series needs a string type.

Thank you very much, Alex.

P.S.
I want a chart sheet, but if the alternative is simpler I'll follow the second one.


John Green wrote:
> Alex,
>
> If your aim is to store a reference to the new chart I suggest creating an object variable.
>
>
> Set myChart = Charts.Add
>
> With myChart
> .Name = "Pippo"
> .ChartType = xlSurface
> End With
>
> Are you creating a chart sheet or an embedded chart in a worksheet? There are differences in the code required.
>

Alex,

You can use the range object directly as in my second reply to your previous post.

Remove the .Location line to create a chart sheet.

--

John Green - Excel MVP
Sydney
Australia


"y" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Thank you John,
> I'll try your suggestions.
>
> First of all I bypassed the problem postponing the the instruction with the xlSurface to the bottom
> of my macro. It works.
>
> Second, I would ask you how to convert a range reference to a string within a macro.
> The instruction to set the data series needs a string type.
>
> Thank you very much, Alex.
>
> P.S.
> I want a chart sheet, but if the alternative is simpler I'll follow the second one.
>
>
> John Green wrote:
> > Alex,
> >
> > If your aim is to store a reference to the new chart I suggest creating an object variable.
> >
> >
> > Set myChart = Charts.Add
> >
> > With myChart
> > .Name = "Pippo"
> > .ChartType = xlSurface
> > End With
> >
> > Are you creating a chart sheet or an embedded chart in a worksheet? There are differences in the code required.
> >
>

Great!

Anyway as you noted my area has both data and label. I'm not experienced of object and how treat
them. Where can I get references about charts and their properties?

Shortly, I want to manage the X axis values and and labels (my Y axis values). How may I do that?
How to use the information get from user ( Inputbox(prom.... ) to set Xvalues and the labels?

If I want to manage other features of a graph?

Thanks Alex.

John Green wrote:
> The following code works for me under Excel 2003
>
> Sub Chart2Dsurface()
>
> Dim sArea As Range
> Dim i As Integer
> Dim Nseries As Integer
> Dim myChart As Chart
>
> Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
> Nseries = sArea.Rows.Count - 1
>
> Set myChart = Charts.Add
> With myChart
> .SetSourceData Source:=sArea
> .Name = "Pippo"
> .ChartType = xlSurface
> .Location where:=xlLocationAsObject, Name:="Sheet2"
> End With
>
> End Sub