Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Identify User Defined Charts (Excel 2003)

    Can anyone help?

    I have created some User Defined Charts that are stored in a separate file, with code that copies them to the XLUSRGAL.xls file (as I do not want the file to be overwritten). As the separate file loads the charts are copied. What I need to know is if I can check to see whether the chart already exists so that I do not copy over if it is not necessary. I have looked to see if I can identify User Defined Charts using the xlUserDefined under ChartTypes but no joy. I have also looked at CustomChartStyles but am unable to retrieve any information - possibly looking in completely the wrong place !!

    Please shout if I'm not making sense !!

    Thanks
    Jack

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify User Defined Charts (Excel 2003)

    Does this work?

    Dim cht As Chart
    For Each cht In Workbooks("xlusrgal.xls").Charts
    Debug.Print cht.Name
    Next cht

  3. #3
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify User Defined Charts (Excel 2003)

    Hans ... you are an absolute star. Works like a dream.

    Many thanks
    Jack

  4. #4
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify User Defined Charts (Excel 2003)

    Morning Hans

    I am using your code to identify if a particular chart type is available within xlusrgal.xls (and it works a treat). I do appear to have a couple of issues though, the first being if the file does not exist (Err.number = 9, Err.description = Subscript out of range) which I am able to trap. The second being another error which I am unable to trap and am unsure why it occurs:

    Err.number = -2147352565
    Err.description = Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus.

    Do you have any ideas what this error is referring to? I have checked on the internet but cannot seem to find an answer.

    Many thanks as always
    Jack

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify User Defined Charts (Excel 2003)

    Where does this error occur?

  6. #6
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify User Defined Charts (Excel 2003)

    The error occurs on the first line of this code (when it is looking to access the file).

    For Each chtChart In Workbooks("xlusrgal.xls").Charts
    If InStr(1, chtChart.Name, "Brand", vbTextCompare) = 1 Then
    BrandChartExists = True
    Exit Function
    End If
    Next chtChart

    I've also noted that if I have a created xlsurgal.xls file (say with one user defined chart) I still get the Err 9 (Subscript out of range), so my thinking behind this error being that the file does not exist is obviously wrong.

    Many thanks
    Jack

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identify User Defined Charts (Excel 2003)

    The xlusrgal.xls workbook is not loaded when you start Excel; it is loaded the first time you use the chart wizard. So if you run this code in an Excel session in which the chart wizard hasn't been used yet, you'll get error 9 since xlusrgal.xls is not part of the Workbooks collection.

    I have no idea why the other error you mention would occur on the For Each chtChart ... line.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identify User Defined Charts (Excel 2003)

    Thanks Hans .... It's all a bit clearer now. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Regards
    Jack

Posting Permissions

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