DataLabels

G

Guest

Windows 2000 Excel XP

All of the code except this part (see full code at end):
ActiveChart.SeriesCollection(strSeries).DataLabels(i).Font = 2
.DataLabels.ColorIndex = 2
.DataLabels.Background = xlTransparent
.DataLabels.HorizontalAlignment = xlCenter
.DataLabels.VerticalAlignment = xlCenter
.DataLabels.Position = xlLabelPositionCenter
.DataLabels.Orientation = xlHorizontal

I want to change the DataLabels Font color, Background and alignment. Do
not know the syntax as I keep getting an error message.
Any help would be appreciated ... thanks..



Private Sub cmdRun_Click()
Dim strSeries As String
Dim DLRange As Range
Dim Pts As Integer
Dim i As Integer
Dim intValue As Integer
'To change the background color of the Series
' change the Case numbers to what is needed
' change the colorindex to what is needed
' for color indexes see ColorIndex Sheet

On Error GoTo Errorhandler

With Selection
strSeries = .Name
End With
'MsgBox strSeries
Set DLRange = Range(RefLabel.Text)

Pts = ActiveChart.SeriesCollection(strSeries).Points.Count
For i = 1 To Pts
ActiveChart.SeriesCollection(strSeries).Points(i).HasDataLabel = True
Next i

For i = 1 To Pts
With ActiveChart.SeriesCollection(strSeries).Points(i)
intValue = DLRange(i).Value
Select Case intValue
Case Worksheets("Color Index").Range("B2").Value To Worksheets("Color
Index").Range("C2").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D2").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B3").Value To Worksheets("Color
Index").Range("C3").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D3").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B4").Value To Worksheets("Color
Index").Range("C4").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D4").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B5").Value To Worksheets("Color
Index").Range("C5").Value
' .MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D5").Value
' .Pattern = xlSolid
.PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B6").Value To Worksheets("Color
Index").Range("C6").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D6").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B7").Value To Worksheets("Color
Index").Range("C7").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D7").Value
'.Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B8").Value To Worksheets("Color
Index").Range("C8").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D8").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B9").Value To Worksheets("Color
Index").Range("C9").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D9").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B10").Value To Worksheets("Color
Index").Range("C10").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D10").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B11").Value To Worksheets("Color
Index").Range("C11").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D11").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B12").Value To Worksheets("Color
Index").Range("C12").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D12").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B13").Value To Worksheets("Color
Index").Range("C13").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D13").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B14").Value To Worksheets("Color
Index").Range("C14").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D14").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B15").Value To Worksheets("Color
Index").Range("C15").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D15").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B16").Value To Worksheets("Color
Index").Range("C16").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D16").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B17").Value To Worksheets("Color
Index").Range("C17").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D17").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B18").Value To Worksheets("Color
Index").Range("C18").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D18").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B19").Value To Worksheets("Color
Index").Range("C19").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D19").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B20").Value To Worksheets("Color
Index").Range("C20").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D20").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B21").Value To Worksheets("Color
Index").Range("C21").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D21").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B22").Value To Worksheets("Color
Index").Range("C22").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D22").Value
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B23").Value To Worksheets("Color
Index").Range("C23").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D23").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B24").Value To Worksheets("Color
Index").Range("C24").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D24").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B25").Value To Worksheets("Color
Index").Range("C25").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D25").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B26").Value To Worksheets("Color
Index").Range("C26").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D26").Value
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B27").Value To Worksheets("Color
Index").Range("C27").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D27").Value
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic

Case Else
'.MarkerBackgroundColorIndex = xlNone
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
End Select

'ActiveChart.SeriesCollection(strSeries).Points(i).DataLabel.Text =
DLRange(i)
'ActiveChart.SeriesCollection(strSeries).Points(i).DataLabel.Font = 2
.DataLabel.Text = DLRange(i)
ActiveChart.SeriesCollection(strSeries).DataLabels(i).Font = 2

.DataLabels.ColorIndex = 2
.DataLabels.Background = xlTransparent
.DataLabels.HorizontalAlignment = xlCenter
.DataLabels.VerticalAlignment = xlCenter
.DataLabels.Position = xlLabelPositionCenter
.DataLabels.Orientation = xlHorizontal

End With
Next i

Exit Sub
Errorhandler: 'Error-handling routine
Select Case Err.Number 'Evaluate error number
Case 91 'No Series selected
MsgBox Err.Number & " An Error has occurred! Please make sure a Series was
Selected."
Case 1004
MsgBox Err.Number & " An Error has occurred! Possible Range Error"
Case Else
MsgBox Err.Number & Err.Description
End Select
End Sub
 
G

Guest

FGM said:
Windows 2000 Excel XP

All of the code except this part (see full code at end):
ActiveChart.SeriesCollection(strSeries).DataLabels(i).Font = 2
.DataLabels.ColorIndex = 2
.DataLabels.Background = xlTransparent
.DataLabels.HorizontalAlignment = xlCenter
.DataLabels.VerticalAlignment = xlCenter
.DataLabels.Position = xlLabelPositionCenter
.DataLabels.Orientation = xlHorizontal

I want to change the DataLabels Font color, Background and alignment. Do
not know the syntax as I keep getting an error message.
Any help would be appreciated ... thanks..



Private Sub cmdRun_Click()
Dim strSeries As String
Dim DLRange As Range
Dim Pts As Integer
Dim i As Integer
Dim intValue As Integer
'To change the background color of the Series
' change the Case numbers to what is needed
' change the colorindex to what is needed
' for color indexes see ColorIndex Sheet

On Error GoTo Errorhandler

With Selection
strSeries = .Name
End With
'MsgBox strSeries
Set DLRange = Range(RefLabel.Text)

Pts = ActiveChart.SeriesCollection(strSeries).Points.Count
For i = 1 To Pts
ActiveChart.SeriesCollection(strSeries).Points(i).HasDataLabel = True
Next i

For i = 1 To Pts
With ActiveChart.SeriesCollection(strSeries).Points(i)
intValue = DLRange(i).Value
Select Case intValue
Case Worksheets("Color Index").Range("B2").Value To Worksheets("Color
Index").Range("C2").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D2").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B3").Value To Worksheets("Color
Index").Range("C3").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D3").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B4").Value To Worksheets("Color
Index").Range("C4").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D4").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B5").Value To Worksheets("Color
Index").Range("C5").Value
' .MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D5").Value
' .Pattern = xlSolid
.PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B6").Value To Worksheets("Color
Index").Range("C6").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D6").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B7").Value To Worksheets("Color
Index").Range("C7").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D7").Value
'.Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B8").Value To Worksheets("Color
Index").Range("C8").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D8").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B9").Value To Worksheets("Color
Index").Range("C9").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D9").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic

Case Worksheets("Color Index").Range("B10").Value To Worksheets("Color
Index").Range("C10").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D10").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B11").Value To Worksheets("Color
Index").Range("C11").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D11").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B12").Value To Worksheets("Color
Index").Range("C12").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D12").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B13").Value To Worksheets("Color
Index").Range("C13").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D13").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B14").Value To Worksheets("Color
Index").Range("C14").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D14").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B15").Value To Worksheets("Color
Index").Range("C15").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D15").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B16").Value To Worksheets("Color
Index").Range("C16").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D16").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B17").Value To Worksheets("Color
Index").Range("C17").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D17").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B18").Value To Worksheets("Color
Index").Range("C18").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D18").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B19").Value To Worksheets("Color
Index").Range("C19").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D19").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B20").Value To Worksheets("Color
Index").Range("C20").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D20").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B21").Value To Worksheets("Color
Index").Range("C21").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D21").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B22").Value To Worksheets("Color
Index").Range("C22").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D22").Value
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B23").Value To Worksheets("Color
Index").Range("C23").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D23").Value
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B24").Value To Worksheets("Color
Index").Range("C24").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D24").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B25").Value To Worksheets("Color
Index").Range("C25").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D25").Value
' .Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B26").Value To Worksheets("Color
Index").Range("C26").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D26").Value
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
Case Worksheets("Color Index").Range("B27").Value To Worksheets("Color
Index").Range("C27").Value
.MarkerBackgroundColorIndex = Worksheets("Color
Index").Range("D27").Value
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic

Case Else
'.MarkerBackgroundColorIndex = xlNone
'.Pattern = xlSolid
'.PatternColorIndex = xlAutomatic
End Select

'ActiveChart.SeriesCollection(strSeries).Points(i).DataLabel.Text =
DLRange(i)
'ActiveChart.SeriesCollection(strSeries).Points(i).DataLabel.Font = 2
.DataLabel.Text = DLRange(i)
ActiveChart.SeriesCollection(strSeries).DataLabels(i).Font = 2

.DataLabels.ColorIndex = 2
.DataLabels.Background = xlTransparent
.DataLabels.HorizontalAlignment = xlCenter
.DataLabels.VerticalAlignment = xlCenter
.DataLabels.Position = xlLabelPositionCenter
.DataLabels.Orientation = xlHorizontal

End With
Next i

Exit Sub
Errorhandler: 'Error-handling routine
Select Case Err.Number 'Evaluate error number
Case 91 'No Series selected
MsgBox Err.Number & " An Error has occurred! Please make sure a Series was
Selected."
Case 1004
MsgBox Err.Number & " An Error has occurred! Possible Range Error"
Case Else
MsgBox Err.Number & Err.Description
End Select
End Sub
Click to expand...

After trying things all morning... then posting I found the answer...
.DataLabels.Font.ColorIndex = 2
the rest worked...
Maybe it will help someone else.
thanks...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conflict 8
Help with mess of code 1
Centering Text Not Working 5
Macro, Excel: insert cell between colored cells ?? 3
Loop Macro to add Formula only on rows with data 2
Changing cell colour depending on content?? 7
Text search only finds 1st result 6
Apply format to >0 cells 6

Top