Displaying a chart in a User Form

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-9 shows an example.

Showing Graph Userform
Figure 18-9: 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 cursor 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 cursor 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 shown in Listing 18-3 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.

Listing 18-3: Automatically Generating a Chart without User Interaction

Sub CreateChart(r)

Dim TempChart As Chart Dim CatTitles As Range

Dim SrcRange As Range, SourceData As Range

Application.ScreenUpdating = False

Set CatTitles = ActiveSheet.Range("A2:F2")

Set SrcRange = ActiveSheet.Range(Cells(r, 1), Cells(r, 6))

Set SourceData = Union(CatTitles, SrcRange)

Set TempChart = Charts.Add

With TempChart

.ChartType = xlColumnClustered

.SetSourceData Source:=SourceData, PlotBy:=xlRows .HasLegend = False

.PlotArea.Interior.Colorlndex = xlNone .Axes(xlValue).MajorGridlines.Delete .ApplyDataLabels Type:=xlDataLabelsShowValue, _ LegendKey:=False .ChartTitle.Font.Size = 14 .ChartTitle.Font.Bold = True .Axes(xlValue).MaximumScale = 0.6 .Axes(xlCategory).TickLabels.Font.Size = 10 .Axes(xlCategory).TickLabels.Orientation = _ xlHorizontal

.Location Where:=xlLocationAsObject, Name:="Sheet1" End With

' Adjust the ChartObject's size With ActiveSheet.ChartObjects(1) .Width = 300


Listing 18-3 (Continued)

.Height = 150 .Visible = False End With 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 was turned off and its Visible property was set to False.

The final instruction of the ShowChart procedure loads the UserForm. Following is a listing of the UserForm_Initialize procedure. This procedure saves the chart as a GIF file, deletes the ChartObject, and loads the GIF file into the Image control.

Private Sub UserForm_Initialize() Dim CurrentChart As Chart Dim Fname As String

Set CurrentChart = ActiveSheet.ChartObjects(1).Chart Save chart as GIF

Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"

CurrentChart.Export FileName:=Fname, FilterName:="GIF" ActiveSheet.ChartObjects(1).Delete

' Show the chart

Imagel.Picture = LoadPicture(Fname) Application.ScreenUpdating = True End Sub

This workbook is available on the companion CD-ROM.

+1 0

Post a comment