Displaying a chart in a dialog box

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.

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.

Figure 18-12 shows an example, which displays three charts. The Previous and Next buttons switch the displayed chart.

Figure 18-12:

Displaying a chart in a UserForm.

Figure 18-12 shows an example, which displays three charts. 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

Imagel.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.

0 0

Post a comment