Excel Optimize

If you need to display a chart in a UserForm, you find that Excel doesn’t provide any direct way to do so. Therefore, you need to get creative. This section describes a technique that lets you display one or more charts in a UserForm.
Figure 18-12 shows an example, which displays three charts. The UserForm has an Image control. The trick is to use VBA code to save the chart as a GIF file and then specify that file as the Image control’s Picture property. The Previous and Next buttons switch the displayed chart.

In this example, which is also available on this book’s Web site, the three charts are on a sheet named Charts. The Previous and Next buttons determine which chart to display, and this chart number is stored as a Public variable named ChartNum, which is accessible to all procedures. A procedure named UpdateChart, which is listed here, does the actual work.

Private Sub UpdateChart()
Dim CurrentChart As Chart
Dim Fname As String
Set CurrentChart = _
Sheets(“Charts”).ChartObjects(ChartNum).Chart
CurrentChart.Parent.Width = 300
CurrentChart.Parent.Height = 150
‘ Save chart as GIF
Fname = ThisWorkbook.Path & “\temp.gif”
CurrentChart.Export FileName:=Fname, FilterName:=”GIF”
‘ Show the chart
Image1.Picture = LoadPicture(Fname)
End Sub

This procedure determines a name for the saved chart and then uses the Export method to export the GIF file. Finally, it uses the VBA LoadPicture function to specify the Picture property of the Image object.