VBA - Excel Charting help
Question posted by: sitko
(Familiar Sight)
on
July 23rd, 2008 09:02 PM
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:
-
' (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
|
|
July 25th, 2008 01:11 PM
# 2
|
Re: VBA - Excel Charting help
Quote:
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:
-
' (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
|
Hi
Pehaps you need something like this (I cannot see any in your code) to set the datalable value)
- With ActiveChart.SeriesCollection(1) 'selection
-
.HasDataLabels = True 'selection
-
.Name = "Data Label Series 1 Name"
-
' .........
-
'etc
-
-
End With
???
MTB
 |
Not the answer you were looking for? Post your question . . .
189,074 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).
|
|
|
Latest Articles: Read & Comment
Top Visual Basic Forum Contributors
|
