Hi,
I'm trying to create a chart in VBA in an excel sheet. When I manually create it, it creates it just find, but for some reason, when I try to automate its creation, it doesn't include both values in the Datalabels, here is the code I use:
I'm trying to create a chart in VBA in an excel sheet. When I manually create it, it creates it just find, but for some reason, when I try to automate its creation, it doesn't include both values in the Datalabels, here is the code I use:
Code:
' (NumBottles is passed in)
Dim wb As Excel.Workbook
Dim posSheet As Worksheet
Dim NodeNum As Integer
Dim ChartTitle As String
Set wb = Application.ActiveWorkbook
Set posSheet = wb.Sheets("Positions")
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection(1).Name = "Filler Head Positions"
ActiveChart.SeriesCollection(1).XValues = posSheet.Range("N3:N" & NumBottles + 2)
ActiveChart.SeriesCollection(1).Values = posSheet.Range("O3:O" & NumBottles + 2)
ActiveChart.Location Where:=xlLocationAsObject, Name:="Positions"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = False
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
With ActiveChart.SeriesCollection(1) 'selection
.HasDataLabels = True 'selection
With .DataLabels
.ShowValue = True
' I wonder if there is some way to force both values to be visible here
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Position = xlLabelPositionBelow
.Orientation = xlHorizontal
End With
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlCircle
.Smooth = False
.MarkerSize = 15
.Shadow = False
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
Comment