Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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