Chart In A Cell

This tutorial shows you how to specify the source data range and the target cell to place the chart and then create a trend chart and place it in that cell with VBA.

To make a chart in a cell, there are two basic actions:

Adjust target cell size to desired dimensions. See Setting Cell Size for guidance on how to set cell size in points.
Generate chart and place in target cell

The Chrt_in_cell.xls file gives an example of how to do both actions with VBA.

Making a Chart and Placing It in Cell with VBA

There are times when it makes sense to place an embedded chart in a cell. If you have several charts that you want to line up vertically or horizontally, placing them in individual cells lets you adjust the size, alignment of all charts at the same time. let's walk through the Chart_In_A_Cell.xls example.

1. Set Cell size - pressing the Adjust Cell Size button brings up an InputBox that asks the User to identify the target cell for sizing by navigating to it.

2. Set Target Cell Dimensions in Points - the User is prompted for target cell height and width dimensions in points.

3. Set Start Cell For Data - the user is next prompted to navigate to start cell for data to be charted. the procedure assumes trend data with dates in user column and data in adjacent column.

4. Select Chart Cell - User is prompted to select chart cell, in this case cell B2.

5. Chart in Cell - Procedure generates chart and places in target cell (B2) with dimensions specified by User, in example 100 points by 100 points.

Note that the font auto scaling has been set to false so that the User can resize row height and column width without affecting font size. Also, this procedure adds the Y value for the last data point.

The User can add the title to the row above the chart and the Y axis label in the column to the left of the chart.

VBA Code

The VBA code for Chrt_in_cell makes an embedded XY trend chart of User specified data and places it in user specified target cell. The code is organized in 12 steps and commented to help you follow the sequence.

1. Get Data to be charted

2. Set x values and X axis min and max values

3. Set Y values and Y axis min and max values, last point Y value for labeling last point

4. Get destination chart cell - chrt_cell

5. Add embedded XY chart. Use chrt-cell width and height to set chartobject size

6. Set chart type, legend

7. Set x and y values, marker format, label last pt

8. Series line format

9. X Axis min and max values, axis format

10. Y Axis format

11. Chart Area font size and interior color

12. Plot Area interior color and plot area size, fraction of chart area

Public Sub place_new_chrt_in_cell()
' |----------------------------------------------------------------------------------------------|
' | Procedure to make an embedded XY trend chart and place it in user specified cell
' | D. Kelly O'Day ProcessTrends.Com
' | 2/15/06
' | Asks user for data-start point
' | Assumes dates in data_start column, values in adjacent column
' |-----------------------------------------------------------------------------------------------|
Dim x_values As Range
Dim y_values As Range
Dim num_pts As Long
Dim sh As Worksheet
Dim user_selection As Range
Dim sel_sheet As String
'1 ********* InputBox To Determine start location for data
    Set data_start = Application.InputBox(prompt:="Select start cell for data set", Type:=8)
    On Error GoTo 0
    If data_start Is Nothing Then
        'Display message if cancel selected
         MsgBox "A range was not selected"
         End
    End If
   ' Go to selected range - get sheet, col, row ids and number of data rows
          Application.Goto reference:=data_start
         data_start.Select
         ActiveSheet.Select
         data_sh = ActiveSheet.Name
         data_col = data_start.Column
         data_row = data_start.Row
          last_Row = Cells(Rows.Count, data_col).End(xlUp).Row
          num_pts = last_Row - data_row
          Set data_sh = ActiveSheet
'2 ********Set x values, x axis min & max values
      Set x_values = Range(data_sh.Cells(data_row, data_col), data_sh.Cells(last_Row, data_col))
             min_x = data_sh.Cells(data_row, data_col)
             max_x = data_sh.Cells(last_Row, data_col)
'3 ******** Set Y values, Y min & max, Last point Y value
     Set y_values = Range(data_sh.Cells(data_row, data_col + 1), data_sh.Cells(last_Row, data_col + 1))
            min_y = Application.Min(y_values)
            max_Y = Application.Max(y_values)
            last_val = data_sh.Cells(last_Row, data_co4l + 1) ' Used to label last point
'4 ********Get destination cell for chart
      Set chrt_cell = Application.InputBox(prompt:="Select Blank Cell for Chart.", Type:=8)
      If chrt_cell Is Nothing Then
           'Display message if cancel selected
            MsgBox "A range was not selected"
           End
     End If
     Application.Goto reference:=chrt_cell
     chrt_cell.Select
     ActiveSheet.Select
     chrt_sh = ActiveSheet.Name
     On Error GoTo 0
'5 ****** Add Chartobject
          With Sheets(chrt_sh).ChartObjects.Add _
             (Left:=chrt_cell.Left, Width:=chrt_cell.Width, _
               Top:=chrt_cell.Top, Height:=chrt_cell.Height)
            .Interior.ColorIndex = xlNone
'6 ****** With Chart: Set chart type , legend
             With .Chart
                .ChartType = xlXYScatter
                .HasLegend = False
               .SeriesCollection.NewSeries
'7 ****With Seriescollection - set x and y values, marker, label last pt
                   With .SeriesCollection(1)
                           .XValues = x_values
                           .Values = y_values
                           .Smooth = False
                           .MarkerStyle = xlCircle
                           .Shadow = False
                           .Points(num_pts).ApplyDataLabels _
                                     Type:=xlDataLabelsShowValue, _
                                     AutoText:=True, LegendKey:=False, ShowCategoryName:=False
'8 ********* Series collection Line format
                    With .Border
                          .ColorIndex = 57
                          .LineStyle = xlNone
                    End With
                 .MarkerStyle = xlCircle
                 .MarkerSize = 2
             End With
'9 ********** Axes - xlCategory
            With .Axes(xlCategory)
                   .MinimumScale = min_x
                   .MaximumScale = max_x
                   .MajorUnit = 10
                   .TickLabels.AutoScaleFont = False
                   .TickLabels.NumberFormat = "m/d"
                        With .Border
                           .Weight = xlHairline
                           .LineStyle = xlContinuous
                           .ColorIndex = 57
                        End With
                 .MajorTickMark = xlOutside
                 .MinorTickMark = xlNone
                 .TickLabelPosition = xlNextToAxis
                  .TickLabels.Font.Size = 8
                  .HasMajorGridlines = False
                  .HasMinorGridlines = False
         End With
'10 ******* Axes - xlValue
        With .Axes(xlValue)
               With .Border
                  .Weight = xlHairline
                 .LineStyle = xlContinuous
               End With
              .MajorTickMark = xlOutside
              .MinorTickMark = xlNone
              .TickLabels.AutoScaleFont = False
              .TickLabelPosition = xlNextToAxis
              .TickLabels.Font.Size = 8
              .HasMajorGridlines = False
              .HasMinorGridlines = False
          End With
'11 *********** Chart area font and interior color
           With .ChartArea
              .Font.Size = 8
               .Border.LineStyle = 0
               .Interior.ColorIndex = xlNone
           End With
'12 ************ Plot area interior color, size, border style
        With .PlotArea
            .Interior.ColorIndex = xlNone
            .Left = 0
             .Top = 0
             .Height = chrt_cell.Height * 0.9
             .Width = 0.9 * chrt_cell.Width
              .Border.LineStyle = 0
        End With
    End With
End With
End Sub