I've created a macro that looks for links in the series name and value formulas for all the charts on a given sheet. The issue is, that in practice, this macro will be run on sheets that have more than one chart. So I'll need to include the name (read Title since the actual name value excel sees isn't helpful to the general user) in the msg box I through at the end of the macro listing all the locations any such links are found so that they can be removed correctly (yes in all cases links are bad for this workbook).
My code as it stands now is below. I've tried a couple ideas where I currently have "ActiveChart.ChartTitle.Characters.Text", but nothing I've come up with has worked thus far.
(Side note the If statement that ends the macro is what I was using to test any ideas that I had/could find on how to capture the title and won't be in the final version)
Thanks for any help!
Sub LookUpLinks()
Dim ChartName As String 'holds the name of a chart so that we can use it to let the user know what
'chart to fix
Dim SeriesName As String 'holds the name of a data sereis so we can let the user know what series in the
'chart needs fixed
Dim i As Integer 'used for controlling loop through charts
Dim j As Integer 'used to control loop through series in a chart
'Dim CurChart As ChartObject 'holds the chart being worked with while we're still working with charts
Dim PlacesBrokenText As String 'Holds a string identifying the places where the
Dim ChartCount As Integer 'Holds the number of charts on the current sheet
'Application.ScreenUpdating = False
'check there are charts
If ActiveSheet.ChartObjects.Count = 0 Then
End
End If
'There are charts. Start PlacesBrokenText, ChartCount, and i
PlacesBrokenText = "Links to other files have been detected in the following locations:" & vbCrLf
i = 1
ChartCount = ActiveSheet.ChartObjects.Count
'Check the charts for links
Do While i <= ChartCount
'Select the chart and reset j
ActiveSheet.ChartObjects(i).Select
If MsgBox(ActiveChart.ChartTitle.Characters.Text, vbYesNo) = vbNo Then
End
End If
j = 1
Do While j <= ActiveChart.SeriesCollection.Count
'Check each series in the chart. We need to go to the next line of code if there's an error, so that
'if there isn't an i-th series we just keep going.
On Error Resume Next
'Check if the series value formula or name formula contains a : as that is the sign of a link
If InStr(ActiveChart.SeriesCollection(j).Formula, ":") = 0 Then 'And
'InStr(ActiveChart.SeriesCollection(j).Name.Formula, ":") = 0 Then
'It doesn't
MsgBox "No link in " & ActiveChart.SeriesCollection(j).Name, vbOKOnly
Else
'It does, capture the series name, and chart name, so that we can let the user know where to fix things
ChartName = ActiveChart.ChartTitle
SeriesName = ActiveChart.SeriesCollection(j).Name
MsgBox "ChartName=" & ChartName & " & SeriesName=" & SeriesName, vbOKOnly
'Now update PlacesBrokenText
PlacesBrokenText = PlacesBrokenText & "- Chart: " & ChartName & " in the series: " & SeriesName & vbCrLf
End If
j = j + 1
Loop
i = i + 1
Loop
'If there are places where it's broken then output PlacesBrokenText, otherwise let the user know Excel is
'being weird.
If Len(PlacesBrokenText) > 69 Then
MsgBox PlacesBrokenText, vbOKOnly
Else
MsgBox "There are no links in any of the charts on this sheet. If you're still getting the error try" _
& " using Ctrl+f to serach for ':/' as all links include the file path down to the drive letter" _
& " meaning immediately after the drive letter will be ':/' which isn't used in any other " _
& "formula." & vbCrLf & vbCrLf & "If you've done such a search already, then it is most likely" _
& " the case that one of the charts USED to have a link and Excel missed reseting the flag for " _
& "the link warning." & vbCrLf & vbCrLf & "In that case go through the charts one by one, delete " _
& "the current version and recreate it from scratch. Once you recreate a chart perform a Save " _
& "and see if the prompt is thrown again. If so, move to the next chart, if not you're golden." _
, vbOKOnly, "No bad charts"
End If
End Sub
Bookmarks