Display chart on a user form in excel

[shep6]

Hi there

I have a user form in Excel 2000 which has a list box, the data source of which is a selection of data in an excel worksheet.

I also have a chart on this worksheet. I would like to display the chart on the user form alongside the list box.

I've looked at the controls available and can't find anything which I can set the data source to the chart (ie image control, text box, label, etc).

Am I missing something / does anyone know if this is possible?

thanks

 

[zerocul]

You can use an image control to display the chart. You must first create the chart on a worksheet within the workbook and export the chart as a gif file. You will set the picture property of the image control to the exported gif file. Use the following web address for detailed information:

http://j-walk.com/ss/excel/tips/tip66.htm

 

[shep6]

Thanks, this works fine, except the chart doesn't look that good as it appears to have "lost" resolution when it was saved as a gif (eg, the x & y axis are unreadable).

Is there any way round this?

I have since been told about the Microsoft Office Web Components - Microsoft Office Chart 9.0 add in. I've had a play around with this, does anyone know if you can simply set the source of this control to an existing chart in a worksheet?

thanks

 

[SkipVought]

Shep,

I played around with this and got as far as displaying a column chart on a userform. I ran AddSeries from the Userform1.Activate event.

BTW, add a reference to MS Graph. I had already added that object to my userform.

Sub AddSeries()
    Dim seriesNames(1)
    Dim categories(7)
    Dim values(7) '
    
    Set cht = ChartSpace1.Charts(0)
    Set c = ChartSpace1.Constants
    cht.Type = c.chChartTypeColumnClustered
    
    seriesNames(0) = [b1]
    i = 0
    For Each r In Sheet1.[Name1]
        categories(i) = r.Value
        i = i + 1
    Next
    i = 0
    For Each r In Sheet1.[Amt]
        values(i) = r.Value
        i = i + 1
    Next
    
    cht.SetData c.chDimSeriesNames, c.chDataLiteral, seriesNames
    cht.SetData c.chDimCategories, c.chDataLiteral, categories
    cht.SeriesCollection(0).SetData c.chDimValues, c.chDataLiteral, values
End Sub

Hope this gives you a start.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884