Displaying A Chart In A Userform

Category: UserForms | [Item URL]

A companion file is available: Click here to download

One of my favorite Excel tricks is to display a chart in a UserForm. You might think that this would be easy: Just add a linked picture of the chart to the UserForm. Unfortunately, that's not possible.

This tip describes a workaround that involves saving the chart as a GIF file, and then displaying the GIF in an Image control. This ensures that the UserForm always displays the current version of the chart.

You might notice a slight delay as the chart is saved and then retrieved. On a fast system, however, this delay is barely noticeable.

How it works

To set this up:

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 the LoadPicture function to do this.
4. Add other bells and whistles as desired. For example, the demo file displays multiple charts, and the user can cycle through them.

Saving a chart as a GIF file

The code below demonstrates how to create a GIF file (named temp.gif) from a chart (the first chart object on Sheet1).

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

Changing the Image control's Picture Property

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

        Image1.Picture = LoadPicture(Fname)