Conditional data labels

[Indigo]

I am running Excel 2003 and I have several charts on one worksheet where the source data comes from named ranges. I have a scroll bar in place for these charts for users to "scroll" through the data as more is added. The problem I have is we want data labels but only on the data points that are more than or less than a specific value. I had an idea that if I created a "hidden" data range where the data is based on an IF statement that would do the trick, but the data labels for the "blank" cells in the data still come into the chart. The labels are the category name not the value. We need to highlight the category name as this indicates a data point of concern if it is above or below a specific value. I need the data label to move with the data point as the scroll bar is advanced.

I went search for some VB code that might do this programmatically and found this:

Private Sub DataLabels()
' Declare all variable
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series

For Each srs In Charts(1).SeriesCollection

With srs
If Not .HasDataLabels Then
.HasDataLabels = True
End If

nPts = .Points.Count
aVals = .Values

For iPts = 1 To nPts
If aVals(iPts) = 0 Then
' If the value is blank or 0, then hide the chart labels
.Points(iPts).HasDataLabel = False
Else
' If there is something great than 0, show all the stuff on the chart
.Points(iPts).ApplyDataLabels xlDataLabelsShowLabelAndPercent, True, False, True, False, True, True, True, True
End If
Next

End With
Next

End Sub

and thought I could adapt it to my 'hidden' data field, but by VB knowledge in Excel is limited. Can anyone point
me in the right direction as I have 36 charts to work with. Thank you.

 

[Indigo]

I think I found something that might be more helpful....

Dim nPts As Integer
Dim aVals As Variant
Dim temp As Double
Dim iPts As Integer

Worksheets(Sheet1).ChartObjects(1).Activate 'assumes first series

With ActiveChart.SeriesCollection(1)
nPts = .Points.Count 'save the number of points
aVals = .Values 'save all the values in array

For iPts = 1 To nPts ' loop through all points
temp = aVals(iPts) 'save value of specific point

If temp > 5 Then
.Points(iPts).ApplyDataLabels AutoText:=True, _
LegendKey:=False, ShowSeriesName:=False, ShowCategoryName:=True, _
ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
End If

Next iPts
End With

But being green to VB in excel, I get a run time error - type mismatch on this line:

Worksheets(Sheet1).ChartObjects(1).Activate 'assumes first series

Can someone help me debug this line? Or how I can point it to a specific series as opposed to assuming "first series"

 

[Indigo]

D'Oh - forgot the quotes:

Worksheets("Sheet1").ChartObjects(1).Activate 'assumes first series

I will get the hang of this

Any other input would be appreciated.

 

[Bilbo_Baggins_Esq]

Just a heads up.

You may want to start finding an alternative to this code.
When you upgrade to Excel 2007 this code will not work.

As of this writing, I have not found a method to edit Office Art (new for Office 2007) objects directly by using VBA.

When you build charts, you CAN use cell references and then edit the cells via VBA.

 

[Indigo]

Thank you Bilbo...although I do not expect my company to move to Office 2007 anytime soon, if you could direct me to some information on cell references and editing the cells via VBA. I learn better if I can see by example.