VBA Chart Formatting: Series Data Labels!!

[lashutm]

I have a macro in Access that takes data, throws it into Excel, and creates a bar graph; all using VBA code.
My final problem involves Series Data Labels (the little numbers above each bar that specifies the corresponding number of what that individual bar represents) on the chart. I.e. If the bar equals 10, the number 10 shows above the bar.

I can get the labels to show, but I would like to format them: make them bold; and move them above the graph bars so they can be read more easily.

Thanks!

For reference, this is how I created one of my data labels:

oChart.SeriesCollection(1).Points(1).HasDataLabel = True

Now how do I format them using VBA?!

 

[Jon Peltier]

You could apply data labels to the entire series using

oChart.SeriesCollection(1).HasDataLabels = True

Turn on the macro recorder, apply the formatting to a sample chart, and you'll see the syntax you need. It will resemble this:

With oChart.SeriesCollection(1).DataLabels
  .Position = xlLabelPositionOutsideEnd
  .Font.Bold = True
End With

 

[lashutm]

Excellent idea, but is not working. . .

The macro recorder shows I need to use ActiveChart, but I get a runtime error. I got this problem before. . .

Runtime Error 91: Object variable or with variable not set. I'm thinking this error is due to ActiveChart and is first prompted at: ActiveChart.ChartType = xl3DColumnClustered.

How do I get ActiveChart working!!!!!!!!!! Thanks!

' ************start of CHART CREATION code
Dim oXL As Object ' Excel application
Dim oBook As Object ' Excel workbook
Dim oSheet As Object ' Excel Worksheet
Dim oChart As Object ' Excel Chart
Const msoFalse = 0
Const msoScaleFromTopLeft = 0

Dim iRow As Integer ' Index variable for the current Row
Dim iCol As Integer ' Index variable for the current Row

Const cNumCols = 4 ' Number of points in each Series
Const cNumRows = 5 ' Number of Series

ReDim aTemp(1 To cNumRows, 1 To cNumCols)

'Start Excel and create a new workbook
Set oXL = CreateObject("Excel.application")
oXL.Application.Workbooks.Add
Set oBook = oXL.Application.ActiveWorkbook
Set oSheet = oBook.Worksheets.Item(1)
'**********************
With oBook 'With objActiveWkb
'************ Create Worksheet Column Headings
Dim i As Integer
Dim j As Integer
For i = 2 To 5
.Worksheets(1).Cells(1, i) = 2003 + i
Next i
'************ Create Worksheet Row Headings
.Worksheets(1).Cells(2, 1) = "Pre-Screen"
.Worksheets(1).Cells(3, 1) = "Selected 1st Rd"
.Worksheets(1).Cells(4, 1) = "Selected 2nd Rd"
.Worksheets(1).Cells(5, 1) = "Offer Extended"
.Worksheets(1).Cells(6, 1) = "Hired"
For i = 1 To 5
For j = 1 To 4
.Worksheets(1).Cells(i + 1, j + 1) = StatsArray(i, j)
Next j
Next i
End With
'************************ ADDS THE CHART: THE CODE I WAS USING BEFORE (COMMENTED OUT)/ I COULD NOT FORMAT THE DATA SERIES LABELS I THINK BECAUSE IT WAS NOT AN ACTIVE WORKSHEET
'Add a chart object to the first worksheet
'Set oChart = oSheet.ChartObjects.Add(50, 100, 700, 300).Chart
'oChart.SetSourceData Source:=oSheet.Range("A1:E6")
'oChart.ChartType = xl3DColumnClustered
'oChart.RightAngleAxes = True
'oChart.HasTitle = True
'oChart.ChartTitle.Caption = "2005-2008 MBA Cascade"
'oChart.ChartArea.Fill.OneColorGradient( ;msoGradientHorizontal,4,0.4)
'oChart.Axes(1, 1).HasTitle = True
'oChart.Axes(1, 1).AxisTitle.Characters.Text = "Recruiting Activity"
'oChart.Axes(2, 1).HasTitle = True
'oChart.Axes(2, 1).AxisTitle.Characters.Text = "Number of Students"
'if you add another series, add another SeriesCollection color
' oChart.SeriesCollection(1).Interior.ColorIndex = 2
'oChart.SeriesCollection(2).Interior.ColorIndex = 10
'oChart.SeriesCollection(3).Interior.ColorIndex = 11
'oChart.SeriesCollection(4).Interior.ColorIndex = 8
'oChart.SeriesCollection(1).HasDataLabels = True
'oChart.SeriesCollection(2).HasDataLabels = True
'oChart.SeriesCollection(3).HasDataLabels = True
'oChart.SeriesCollection(4).HasDataLabels = True

‘*************(SUPPOSED TO) ADDS THE CHART:PROBLEM: THE CODE I GOT FROM THE MACRO/ NOTICE THE ACTIVECHART IN THIS CODE SEQUENCE VERSUS THE OCHART IN THE EXAMPLE DIRECTLY ABOVE THAT I HAD BEEN USING PREVIOUSLY. . .
Charts.Add
ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:E6"), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "2005-2008: MBA Cascade"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Recruiting Activity"
.Axes(xlSeries).HasTitle = False
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Number of Students"
End With
ActiveChart.PlotArea.Select
Selection.Left = 10
Selection.Top = 39
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 3").IncrementLeft -135.75
ActiveSheet.Shapes("Chart 3").IncrementTop 1.5
ActiveSheet.Shapes("Chart 3").ScaleWidth 1.83, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 3").ScaleHeight 1.33, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Characters.Text = "Number of " & Chr(10) & "Students"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=19).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

 

[lashutm]

Bump

 

[Jon Peltier]

The macro recorder shows I need to use ActiveChart

No. The macro recorder shows that you used an active chart while recording. The macro recorder tracks every click, every selection event, blah blah, and your job as a seasoned programmer is to streamline this recorded code. Anyway, the syntax with respect to the active chart is the same as the syntax with respect to any chart, such as the chart variable you were using before (oChart).

The recorded code looks like:

With ActiveChart 
  .HasTitle = True 
  .ChartTitle.Characters.Text = "2005-2008: MBA Cascade" 
  .Axes(xlCategory).HasTitle = True 
  .Axes(xlCategory).AxisTitle.Characters.Text = "Recruiting Activity" 
  .Axes(xlSeries).HasTitle = False 
  .Axes(xlValue).HasTitle = True 
  .Axes(xlValue).AxisTitle.Characters.Text = "Number of Students" 
End With

Change the reference to the active chart, ActiveChart, to a reference to the chart variable, oChart:

With oChart 
  .HasTitle = True 
  .ChartTitle.Characters.Text = "2005-2008: MBA Cascade" 
  .Axes(xlCategory).HasTitle = True 
  .Axes(xlCategory).AxisTitle.Characters.Text = "Recruiting Activity" 
  .Axes(xlSeries).HasTitle = False 
  .Axes(xlValue).HasTitle = True 
  .Axes(xlValue).AxisTitle.Characters.Text = "Number of Students" 
End With