How to integrate a chart in a VBA form ?

Patrick Althaus

unread,

Mar 12, 2001, 3:38:52 AM3/12/01

to

Hello !

I'd like to integrate a graphic chart in a VBA form.
Is there an ActiveX control somewhere which
could help me doing this ? If yes, where can I
fint it ?

Thanx for you answers !

Greetings
Patrick Althaus

Bernard Liengme

unread,

Mar 12, 2001, 10:32:24 AM3/12/01

to Patrick Althaus

Hi Patrick
See my web site for how to compute area under curve:
http://www.stfx.ca/people/bliengme/ExcelTips
Best wishes
Bernard

Patrick Althaus

unread,

Mar 12, 2001, 12:00:57 PM3/12/01

to

Thank you Bernard, but I try to program
that in VBA, not to create in Excel.

Thanx anyway !

Tushar Mehta

unread,

Mar 12, 2001, 12:59:40 PM3/12/01

to

I interpreted your request very differently than Bernard Liengme, in
that you want to do is take a chart and show it in a userform. If that
is correct, read on.

First, in VBE, on a userform, add an image control. If the image
control is not available from the Toolbox toolbar, add it by right-
clicking the toolbar, selecting 'Additional Controls...' and finding the
image control. Then, at run time, use VBA to export the chart to an
image file (such as a jpg). Now, set the image controls picture
attribute to refer to the file just exported.

You can set the file name at design-time and then ensure that you export
the chart as the same file name.

This does *not* create a dynamic link in the sense that if the chart
changes while the userform is still displayed, the chart image within
the form will not update itself.

--
Regards,

Tushar Mehta
http://www.tushar-mehta.com
--
In <98i29r$fsn$3...@bw107zhb.bluewin.ch>, Patrick Althaus
<patrick...@also.ch> wrote

SteveT

unread,

Mar 12, 2001, 3:43:21 PM3/12/01

to

And just to show there are multiple ways to accomplish the same task, if an Excel Chart is not specifically required, and you are using another Office product, then look into Microsoft Graph (check your Insert Object selections).

You can check out the automation help files on the Kb:

Q260410: Auto2000.exe
Q167223: Auto97.exe
Q186855: A97 Grphsm97.exe

"Microsoft Graph allows data to be charted. The functionality is similar to Microsoft Excel's Chart object but require less system resources to load. Since Microsoft Graph 97 is an applet, it must run inside a parent application such as Microsoft Access 97 or Microsoft Word 97. You cannot use Automation code to activate it externally with the CreateObject or GetObject functions. Rather, you must automate it through the container of the parent application, such as an OLE object control in a Microsoft Access form, or a Shape object control in a Microsoft Word document."

Steve

Patrick Althaus <patrick...@also.ch> wrote in message news:98i29r$fsn$3...@bw107zhb.bluewin.ch...

Phillip Ikhile

unread,

Mar 13, 2001, 5:48:33 PM3/13/01

to

Hi Patrick,
a graphic chart Activex Control comes with MSOffice, its called MSChart
(latest version is 9.0)
To use in Excel, right-click on your Controls ToolBox and select MS Chart
from the list offered. It is added to your toolbox, you can the clik it and
draw the chart on your Userform. Check out the ObjectBrowser for the
methods, properties and help .

cheers
Phillip

"Patrick Althaus" <patrick...@also.ch> wrote in message
news:98i29r$fsn$3...@bw107zhb.bluewin.ch...

Patrick Althaus

unread,

Mar 14, 2001, 2:00:46 AM3/14/01

to

Thank you for your answers !

I finally used the MSChart ActiveX control.
(Tools - References - Microsoft Chart Control)
I didn't find it first and then installed VB 5.0.
After that I found it. (I'm still not sure if I really
searched for it very well the first time...)

Here's an example for the OCX :

Private Sub Command1_Click()
With Form1.MSChart1
' 3D diagram with 8 data columns
' and 8 data rows
.ChartType = VtChChartType3dBar
' draw the legend
.Location.Visible = True
.Location.LocationType = VtChLocationTypeRight
' set legend properties
.TextLayout.HorzAlignment = _
VtHorizontalAlignmentRight ' align right
' yellow text
.VtFont.VtColor.Set 255, 255, 0
.Backdrop.Fill.Style = VtFillStyleBrush
.Backdrop.Fill.Brush.Style = VtBrushStyleSolid
.Backdrop.Fill.Brush.FillColor.Set 255, 0, 255
.ColumnCount = 8
.RowCount = 8
' generate random data
For column = 1 To 8
For row = 1 To 8
.Column = column
.Row = row
.Data = row * 10
Next row
Next column
End With
End Sub

Tushar Mehta

unread,

Mar 14, 2001, 7:16:05 AM3/14/01

to

Thanks for sharing what worked.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--
In <98n54j$1fc$1...@bw107zhb.bluewin.ch>, Patrick Althaus
<patrick...@also.ch> wrote

> Thank you for your answers !
>
> I finally used the MSChart ActiveX control.
> (Tools - References - Microsoft Chart Control)
> I didn't find it first and then installed VB 5.0.
> After that I found it. (I'm still not sure if I really
> searched for it very well the first time...)
>
> Here's an example for the OCX :
>

[snip]