Displaying A Chart In A Userform
A companion file is available: Click here to downloadOne 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:
- 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)
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks