Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > DataLabels
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
FGM FGM is offline
external usenet poster
  
Posts: 35
Default DataLabels
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.Cou nt
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
FGM FGM is offline
external usenet poster
  
Posts: 35
Default DataLabels


"FGM" wrote:

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.Cou nt
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


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...

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
2007: .DataLabels.Position MrT Excel Programming 1 November 28th 06 01:44 AM
Overlapping DataLabels [email protected] Excel Programming 0 August 29th 06 01:42 PM
Excel Chart Datalabels Sean Howard Excel Programming 6 April 20th 04 12:04 AM
Datalabels on Scatter Daniel Bonallack[_2_] Excel Programming 1 September 15th 03 10:23 PM
Converting DataLabels to Numbers Dan Gesshel Excel Programming 1 August 1st 03 04:34 AM


All times are GMT +1. The time now is 08:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement