Activating last chart added.

Y

y

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

John Green

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

y

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"

Application.WorksheetFunction.Chart.Activate

Thanks Alex.
 
J

John Green

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

John Green

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
 
Y

y

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

John Green

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

y

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