Help Debugging Custom Data Label Macro

[arndtea]

Hi folks,

I'm pretty new to using VBA and macros in Excel and am hoping someone can help debug a macro I found to create custom data labels for a map. My goal with this is to label a county map of Colorado and make the text large enough to see clearly - it's way too small using the Map Labels option under Format Data Series. If there are other options that are simpler than using a macro, feel free to suggest, but from what I understand the option to create data labels using Value From Cells is not available for MacOS.

Here's what I'm encountering:

1. I followed the process to add and run the macro code using the instructions in the link above and encountered no issues.
2. I added an incorrect data label to the map, selected one as described in step 10 of the instructions, and tried to run the code.
   • EXPECTED: Receive a prompt to select the correct data label range.
   • ACTUAL: Run-time error '445': Object doesn't support this action.
3. Clicked debug to review the code. Line 20 is highlighted, and it reads: If Selection.DataLabels.ShowRange Then
   • Based on my very loose understanding of coding, I'm guessing the issue is a missing argument (not sure if that's the right term) telling the code what to do If the above line is true/false?

Here's the full VBA procedure with line 20 in bold:
Option Explicit

Sub SetCustomDataLabels()
' make sure a series is selected
If TypeOf Selection Is DataLabels Or TypeOf Selection Is Point Then
Selection.Parent.Select
ElseIf TypeOf Selection Is DataLabel Then
Selection.Parent.Parent.Select
End If

If TypeOf Selection Is Series Then
Else
MsgBox "Select a chart series and try again."
Exit Sub
End If

If Selection.HasDataLabels Then
'If the data labels from cells are already showing, stop showing them and exit.
'If labels include other info (e.g., values or categories) this will still appear
If Selection.DataLabels.ShowRange Then
Selection.DataLabels.ShowRange = False
Exit Sub
End If
End If

'Use the InputBox dialog to set the range for the data labels
On Error Resume Next
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Select data label range.", Title:="Data Label Range", Type:=8)
On Error GoTo 0

If rng Is Nothing Then Exit Sub ' clicked cancel
If Selection.HasDataLabels Then
'This will include the new text from cells into existing data labels
Else
'Otherwise add data labels (empty labels)
Selection.HasDataLabels = True
Selection.DataLabels.ShowValue = False
End If

'Create a string that includes the sheet name and range reference.
Dim rngAddress As String
rngAddress = "='" & rng.Worksheet.Name & "'!" & rng.Address(RowAbsolute:=True, ColumnAbsolute:=True, External:=False)
Selection.DataLabels.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, rngAddress, 0
Selection.DataLabels.ShowRange = True

End Sub

Happy to share more info and/or the sheet I'm working in if that's helpful. Thanks all!