Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Unanswered: Error 1004 in name property of series collection

    Hi everbody

    i wrote a short bit of code to update a chart based on a selection from a table and everything was working fine,
    i ran the code again this morning and it failed stating a 1004 error cannot define the name of the series object,
    I have temporarily resolved this issue by breaking all the links that were in the workbook but cant think how to resolve this if links are once again reinserted (as they probably will be when i am no longer in control)

    Does anyone have any ideas how to make this code more stable,

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'code to update a chart based on selection in a table of results
    'if multiple cells selected it will use the top left value
    'written by David Coutts 25/02/2004
        Dim isect           As Range
        Dim LastCol         As Integer
        Dim NewSeries       As String
        Dim NewSeriesName   As String
        
        'get the lastcolumn
        LastCol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
        
        'define variables in case of need
        NewSeries = "='" & Me.Name & "'!R" & Target.Row & "C4:R" & _
                        Target.Row & "C" & LastCol - 1
        NewSeriesName = "='" & Me.Name & "'!R" & Target.Row & "C1:R" & Target.Row & "C2"
        
        
        
        'difference in activity(Table located A6:B531) should not change by definition\
        Set isect = Intersect(Target, Range("A6:B531"))
        'check if selected cell is in the target range
         If Not isect Is Nothing Then
            'alter the chart on the worksheet with the new chart
            'of the updated cells
            With Me.ChartObjects(1).Chart
                'ensure x-axis is of the correct format
                .Axes(xlCategory).TickLabels.NumberFormat = "mmm-yy"
                With .SeriesCollection(1)
                    .Values = NewSeries
                    '*******************************************************************
                    .Name = NewSeriesName 'obtaining error 1004 on this line on occasion
                                        ' With error cannot define name of series collection
                    '*******************************************************************
                    ' if chart activity based colour blue
                    .Interior.ColorIndex = 5
                End With
                'update the chart title
                .ChartTitle.Text = Cells(Target.Row, 1).Value & " " & _
                                    Cells(Target.Row, 2).Value & " Difference in Values"
            End With
        End If
        
        
        'difference in costs(Table located A537:B1062) should not change by definition
        Set isect = Intersect(Target, Range("A537:B1062"))
        If Not isect Is Nothing Then
            With Me.ChartObjects(1).Chart
                'ensure x-axis is of the correct format
                .Axes(xlCategory).TickLabels.NumberFormat = "mmm-yy"
                With .SeriesCollection(1)
                    .Values = NewSeries
                    .Name = NewSeriesName
                    'if the chart cost based then colour Brown
                    .Interior.ColorIndex = 9
                End With
                'update the title of the chart
                .ChartTitle.Text = Cells(Target.Row, 1).Value & " " & _
                                    Cells(Target.Row, 2).Value & " Difference in costs"
            End With
        End If
        
        'get rid of object variables
        Set isect = Nothing
    End Sub
    Cheers
    Dave

  2. #2
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    The only thing i can think of is to put a handler in there that checks whether the series name is valid or not. If it is then continue, else do something that will hopefully invoke it to work.

    On a side note,

    So this is where you've been hiding Dave, I thought you'd vacated the forums. Hows the missus and the wee guy doing? have you all settled in Cheltenham fine?

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Cruickshanks it should be valid ive added a routine to break the links on opening hence it shouldnt have many more problems now,
    but any other improvements in general will be greatly recieved,

    [side note]
    i pretty much hold the fort in this forum, Shades answers when he has the time and SavBill answers every so often but usually its me (dont know where everyone went) probably explains the inflated post count, you know the wee dots you get on threads youve posted in i think i have one on most threads for a few pages

    as for cheltenham, were going to move back to aberdeen after im finished my contract here so ill be back up in about July, nuff said

  4. #4
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Nice one.

    As for your code, i think i'll just fire off ideas at you and let you figure out how to overcome them. I mean, i've pretty much been concentrating on Access since i joined this forum, and i've built a couple of decent apps, but trying to get a query to sort a form based on two text box values.... impossible.

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    well im really an excel man, but could you not build your query with 2 fields in your order by like but not quite like this

    Code:
    SELECT DISTINCT HSMOA, PDSEX
    FROM Provider_APC_0405
    ORDER BY HSMOA ASC, PDSEX asc
    Just a little SQL Server SQL for something im doing at the mo

  6. #6
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    are you suggesting an sql statement within an sql statement?!

    I built a query that runs when you type in the values, but the second you come to run it off the forms, Error 2450: can't find the form "Whajamacallit".

    I could have probably mananged this ages ago, but i think it could be because one subform is referencing another subform.

    you'll see what i mean if you look here: http://www.dbforums.com/t1119391.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •