How to create a bar chart/graph in a VBE dialog box?

[Goh, Yong Kwang]

Hi.

I'm trying to use Visual Basic for Excel to build a frontend user
interface for my backend Excel spreadsheet for the user. Therefore,
for certain features of this frontend, I need to put a chart/graph in
a dialog box created in VBE.

Using the Charts.Add method, it adds the chart to the active worksheet
and not to the dialog box I created in VBE. My idea is to hide the
workbook (and worksheets) such that the user need not key in his/her
input into the worksheets which are doing the calculation.

Thus, I believe that maybe I've to add in a MSChart control or Graph
control. But the Graph control that comes with MS Office requires the
data source to be from a Database Data Source Name (DSN) and can only
be exported to a HTML file or uploaded to a web server.

How do I link in simple chart in a VBE dialog box to some cells in a
worksheet?

Goh Yong Kwang
(e-mail address removed)
Singapore

 

[Jan Karel Pieterse]

Hi Yong,

You need to create the chart in a (hidden) sheet, then saveas that
chart as a picture, then have a picture control on your form you update
with that file. See this page for a how-to:

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

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Robin Hammond]

Yong Kwang,

I don't think you can per se.

Two approaches depending on how much speed you want.

1. http://j-walk.com/ss/excel/tips/tip66.htm has information on saving a
chart to a gif file then pasting the gif into a picture control on a
userform.
2. Same approach with the picture control, but if you want it a lot faster
and have an idea of what you are doing, have a look at Stephen Bullen's
PastePicture routine, which allows you to copy a chart to the clipboard,
then back onto the form. His site appears to be down right now, but it's at
www.bmsltd.co.uk.

The XspandXL add-in on my site uses the second approach, although the charts
are actually pasted into an imagelist control, then from there onto the
userform.

Robin Hammond
www.enhanceddatasystems.com

 

[Stephen Bullen]

Hi Robin,

2. Same approach with the picture control, but if you want it a lot faster
and have an idea of what you are doing, have a look at Stephen Bullen's
PastePicture routine, which allows you to copy a chart to the clipboard,
then back onto the form. His site appears to be down right now, but it's at
www.bmsltd.co.uk.

My .co.uk is no longer under my control, but the files can be downloaded from
my .ie site:

http://www.bmsltd.ie

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie