Macro Problem, (Run time error 5)Invalid Procedure call or argument

[Nitha120]

Hi guy I been stuck with this macro for days now and not able to resolve it please help.

My codes:

Sub time_vs_strain()

Dim chartData As Range
Dim worksheetName As String

Range("B8:E8").Select
Range(Selection, Selection.End(xlDown)).Select
Set chartData = Selection
worksheetName = ActiveSheet.Name
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=chartData, PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
ActiveChart.Location Where:=xlLocationAsObject, Name:=worksheetName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "time vs strain"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "time "
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "strain"
End With

End Sub

when i ran the macro it give me the error:
"Run time error 5, Invalid Procedure call or argument"

it hightlight the line:

ActiveChart.Location Where:=xlLocationAsObject, Name:=worksheetName

Can you guys please help me. I dont know what to do anymore!

 

[TonyJollans]

Hi Nitha120,

It works for me (in 2000 and 2003)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Nitha120]

mine still dont work , I have Excel 2002
I think it has somethimg to do with the length of the file
I have this name for my files,
5-3-05 X NO 2 1.65 DO 0.05-S 21
however when i reduce the length to,
5-3-05 X N 2 1.65 D0.05-S 21

it run fine.

Is there a limit to number length or characters that one can used when naming a file in Excel?
I try to switch the worksheet declare from a String to a Variant yet still have no luck.

Any suggestion would be really great.

 

[TonyJollans]

Hi Nitha120,

When you say 'file' I presume you mean sheet. And I get the same problem with a 31-character sheet name; looks like a feature . Afraid I can offer no solution - except limiting your names to 30 characters.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[