Displaying a Chart in a UserForm

In Chapter 15, I describe a way to display a chart in a UserForm. The technique saves the chart as a GIF file and then loads the GIF file into an Image control on the UserForm.

The example in this section uses that same technique but adds a new twist: The chart is created on the fly and uses the data in the row of the active cell . Figure 18-13 shows an example.

Figure 18-13: The chart in this UserForm is created on the fly from the data in the active row.

The UserForm for this example is very simple. It contains an Image control and a CommandButton (Close). The worksheet that contains the data has a button that executes the following procedure:

 Sub ShowChart()     Dim UserRow As Long     UserRow = ActiveCell.Row     If UserRow < 2 Or IsEmpty(Cells(UserRow, 1)) Then         MsgBox "Move the cell pointer to a row that contains data."         Exit Sub     End If     CreateChart (UserRow)     UserForm1.Show End Sub 

Because the chart is based on the data in the row of the active cell, the procedure warns the user if the cell pointer is in an invalid row. If the active cell is appropriate, ShowChart calls the CreateChart procedure to create the chart and then displays the UserForm.

The CreateChart procedure accepts one argument, which represents the row of the active cell. This procedure originated from a macro recording that I cleaned up to make more general.

 Sub CreateChart(r)     Dim TempChart As Chart     Dim CatTitles As Range     Dim SrcRange As Range, SourceData As Range     Dim FName As String     Set CatTitles = ActiveSheet.Range("A2:F2")     Set SrcRange = ActiveSheet.Range(Cells(r, 1), Cells(r, 6))     Set SourceData = Union(CatTitles, SrcRange) '   Add a chart     Application.ScreenUpdating = False     Set TempChart = ActiveSheet.Shapes.AddChart.Chart     TempChart.SetSourceData Source:=SourceData '   Fix it up     With TempChart         .ChartType = xlColumnClustered         .SetSourceData Source:=SourceData, PlotBy:=xlRows         .HasLegend = False         .PlotArea.Interior.ColorIndex = xlNone         .Axes(xlValue).MajorGridlines.Delete         .ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False         .Axes(xlValue).MaximumScale = 0.6         .ChartArea.Format.Line.Visible = False     End With '   Adjust the ChartObject's size size     With ActiveSheet.ChartObjects(1)         .Width = 300         .Height = 200     End With '   Save chart as GIF     FName = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"     TempChart.Export Filename:=FName, filterName:="GIF"     ActiveSheet.ChartObjects(1).Delete     Application.ScreenUpdating = True End Sub 

When the CreateChart procedure ends, the worksheet contains a ChartObject with a chart of the data in the row of the active cell. However, the ChartObject is not visible because ScreenUpdating is turned off. The chart is exported and deleted, and ScreenUpdating is turned back on.

The final instruction of the ShowChart procedure loads the UserForm. Following is the UserForm_Initialize procedure. This procedure simply loads the GIF file into the Image control.

 Private Sub UserForm_Initialize()     Dim FName As String     FName = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"     UserForm1.Image1.Picture = LoadPicture(FName) End Sub 

CD-ROM  

This workbook, named  chart in userform.xlsm , is available on the companion CD-ROM.