Using VBA to Loop Through and Automatically Position Data Labels?

jim1234

New Member
Joined
Nov 1, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Hello - I am VBA novice, trying to make a macro that can loop through and automatically position data labels in a simple bar chart.

For the most part, outside end data labels are fine. However, for some values that are *slightly* negative, the data labels are overlapping with the X-Axis labels.

So I'd like to write something along the lines of the following:

* Loop through all data labels
* If bar < 0 & > -40, position data label at x position (I'd like to align with the -40 Y axis label)
* Else xlLabelPosistionOutsideEnd

I took a stab at writing this but its a little shoddy. I don't think I'm properly looping through the data points (I have to select all labels before running the macro for it to not send an error message), and I'm not sure the Selection.Top piece is what I want to place the data label where I want:

Sub Macro4()

Dim mychart As ChartObject
Set mychart = ActiveSheet.ChartObjects("Chart 4")


With mychart.Chart.SeriesCollection(1)
Dim myvalues
myvalues = .Values

Dim i As Long

For i = LBound(myvalues) To UBound(myvalues)

If .Points(i).HasDataLabel And myvalues(i) < 0 And myvalues(i) > -40 Then

Selection.Position = xlLabelPositionOutsideEnd
Selection.Top = 146.623

Else

Selection.Position = xlLabelPositionOutsideEnd

End If
Next
End With


End Sub

Any help you guys can give me here would be really appreciated.

Thanks!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jim1234

New Member
Joined
Nov 1, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Update: I think I am pretty close to I want here. This should be a lot cleaner than above... I have just one small issue to iron out now, which is highlighted in red. I am getting an error message there. I am trying to see if data point i (there are 12 data points, so for loop is for i = 1 to 12) fits the conditions, and if it does then move it lower down than xlLabelPosistionOutsideEnd would place it...

Thank you in advance.

Sub Macro8()
'
' Macro8 Macro
'
Dim i As Integer


For i = 1 To 12

ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Select

If ActiveChart.FullSeriesCollection(1).Points(i) < 0 And ActiveChart.FullSeriesCollection(1).Points(i) > -40 Then

Selection.Position = xlLabelPositionOutsideEnd
Selection.Top = 146.623

Else

Selection.Position = xlLabelPositionOutsideEnd

End If

Next i

End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,495
Messages
5,832,025
Members
430,104
Latest member
briannnnleong

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top