0

I am trying to set the values on markers in an Excel sheet, using a custom-range.

I am using a script, which is run on the Workbook_Open event.

The code runs, however the values from the custom-range are not displayed on the markers.

No values are displayed at all.

My VBA code is:

' The open event.
Private Sub Workbook_Open()

' Add a chart.
ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select

' Give the chart a name.
Dim ch As Shape
Set ch = ActiveSheet.Shapes(1)
ch.Name = "ScatterChart"

' Set the chart range etc.
ActiveChart.SetSourceData Source:=Range("'MySheet'!$C$1:$D$12")
ActiveChart.SetElement (msoElementDataLabelCenter)
ActiveChart.FullSeriesCollection(1).Select

' Add some styling for the markers.
With Selection
    .MarkerStyle = 8
    .MarkerSize = 15
    .Format.Fill.Visible = msoFalse
    .MarkerForegroundColor = vbBlack
End With

' *** Try to set the values on the markers. (Issue is somewhere in here.) ***
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.FullSeriesCollection(1).DataLabels.Select
ActiveChart.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, "='MySheet'!$B$1:$B$12", 0
Selection.ShowRange = True
Selection.ShowValue = False
Selection.Position = xlLabelPositionCenter

' Shift focus to the first cell.
Range("A1").Select

End Sub

Thank you for any assistance.

AndyS
  • 53
  • 6
  • 1
    I’m voting to close this question because it looks like ChatGPT generated code. – dwirony Apr 03 '23 at 14:46
  • @BigBen I thought all ChatGPT generated text was banned according to meta, but maybe that's just answers? – dwirony Apr 03 '23 at 14:49
  • @dwirony - Much as I *really* don't like the posting of ChatGTP code in questions/answers, I'm not sure there's any way for us to know the origin of any specific piece of code, and it's a little uncharitable to the poster to accuse them unless it's really clear-cut. This could easily be slightly-edited macro recorder code. – Tim Williams Apr 03 '23 at 15:47
  • First steps to try to fix your code - review this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=Saves_AllUserSaves and try to remove all Select/Activate from your code. – Tim Williams Apr 03 '23 at 15:51
  • @TimWilliams Thank you for the reminder! I was rusty on VBA. All fine now. – AndyS Apr 05 '23 at 15:13

0 Answers0