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.
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:
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
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
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
.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.
Was this article helpful?