Displaying a Chart in a UserForm

Oddly, Excel provides no direct way to display a chart in a UserForm. You can, of course, copy the chart and paste it to the Picture property of an Image control, but this creates a static image of the chart, so it won't display any changes that are made to the chart.

This section describes a technique to display a chart in a UserForm. Figure 15-25 shows a UserForm with a chart displayed in an Image object. The chart actually resides on a worksheet, and the UserForm always displays the current chart. This technique works by copying the chart to a temporary graphics file and then using the LoadPicture function to specify that file for the Image control's Picture property.

Figure 15-25: With a bit of trickery , a UserForm can display "live" charts .

CD-ROM  

This workbook is available on the companion CD-ROM. The filename is  chart in userform.xlsm .

General steps to display a chart in a userform

To display a chart in a UserForm, follow these general steps:

1. Create your chart or charts as usual.

2. Insert a UserForm and then add an Image control.

3. Write VBA code to save the chart as a GIF file and then set the Image control's Picture property to the GIF file. You need to use VBA's LoadPicture function to do this.

4. Add other bells and whistles as desired. For example, the UserForm in the demo file contains controls that let you change the chart type. Alternatively, you could write code to display multiple charts.

Saving a chart as a GIF file

The following code demonstrates how to create a GIF file (named temp.gif ) from a chart (in this case, the first chart object on the sheet named Data ):

 Set CurrentChart = Sheets("Data").ChartObjects(1).Chart Fname = ThisWorkbook.Path & "\temp.gif" CurrentChart.Export FileName:=Fname, FilterName:="GIF" 

Changing the Image control Picture property

If the Image control on the UserForm is named Image1 , the following statement loads the image (represented by the Fname variable) into the Image control:

 Image1.Picture = LoadPicture(Fname) 

Note  

This technique works fine, but you might notice a slight delay when the chart is saved and then retrieved. On a fast system, however, this delay is barely noticeable.