Macro Error 5

[Nitha120]

Hi guys,
I been stuck in this problem for 4 days now and seem to never get it going...i have this error state:

Run time error 5
Invalid procedure call or argument

when i look at my macro file it highlight"

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

for some reason my code will work fine for some worksheet it make the chart as i wanted but some it give this error, could you guy please help me.

 

[SkipVought]

1. Is the chart active?

2. what is the value of worksheetName and does that correcpond to a worksheet name in this workbook?

You might also post ALL your code for this chart so we can see what statements preceed this error.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[Nitha120]

Hi,
here are my codes

Sub Time_vs_Strain()
'declaration of vairable types
Dim chartData As Range
Dim worksheetName As String
' do not change this part deal with selecting the column

Range("B8").Select 'Initial location
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

' set chartData base on selection highlight rather then the range

Set chartData = Selection
worksheetName = ActiveSheet.Name
' need to change the Source and Name as global varialbe
' everything else stay the same

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=chartData, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=worksheetName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Time vs. Stress"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stress"
End With

ActiveChart.SeriesCollection(3).Select
Selection.Delete
ActiveChart.SeriesCollection(1).Select
Selection.Delete

End Sub

All I am trying to do is make a chart for time vs strain it work for a certain data set but it give error for other. I am thinking it might having something to do with out of range..

 

[SkipVought]

I made a minor change, dummied up some data and the code works without error thru the Location change

    Dim chartData As Range
    Dim worksheetName As String
    
' do not change this part deal with selecting the column

    Set chartData = Range([B8], [B8].End(xlToRight))  'Initial location
    Set chartData = Range(chartData, chartData.End(xlDown))
    
    worksheetName = ActiveSheet.Name

' need to change the Source and Name as global varialbe
' everything else stay the same

    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=chartData, PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:=worksheetName
...

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[SkipVought]

DELETE the EMPTY ROW (ROW 9)

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[Nitha120]

I think i know the problem, when i change the worksheet name to something else, shorter and less character it work.
Does anybody know a code i can use to declare the worksheetName as something else so that no matter how many character i choose for my worksheetname it will still work?

 

[SkipVought]

What name are you using that does not work?

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[Nitha120]

i used the name like

3-30-05 X DOUGH FRESH 0.0005 S-1-2

i later found out by reduce the naming i was able to graph just fine.


on other notes Skip, how do i change color of series when i graph all in one?

I have 5 graph with and i graph it all together but each line is same color i was wondering what codes do u add so that each graph in chart have different color?

any sugesstion is needed! thank you

 

[SkipVought]

You have 5 SERIES in your CHART?

Select any SERIES -- Format/Selected series...

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[Nitha120]

thanks for the help Skip