Displaying a Chart in a UserForm
One of my favorite Excel techniques is to display a chart in a custom dialog box. If you use an Excel 5/95 DialogSheet (rather than an Excel UserForm), the procedure is relatively simple: Copy the chart as a picture, and use paste it to the DialogSheet (using a link, if desired). Problem is, DialogSheets are rapidly becoming obsolete.
If you use an Excel 97 or later UserForm, you'll find that you cannot paste linked pictures. 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.
Download an example
You can download an example file that demonstrates the technique described here.
How it works
To set this up:
- Create your chart or charts as usual.
- Insert a UserForm and then add an Image control.
- 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.
- 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)
Caveat
This technique works fine, but you will notice a slight delay as the chart is saved and
then retrieved. On a fast system, however, this delay is barely noticeable.