Microsoft Office Tutorials and References
In Depth Information
Displaying a Chart in a UserForm
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 = Application.DefaultFilePath & 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 isn’t visible because
ScreenUpdating is turned off. The chart is exported and deleted, and ScreenUpdating is
turned back on.
 
Search JabSto ::




Custom Search