Search code examples
excelvbachartssendkeysunlink

Unlinking Excel Chart using VBA

Hi i have been looking for a solution to unlink my chart (within the same worksheet) from the values. For this I would usually select the data, go into formulas (F2), press F9 and enter

my series would be turned into numbers. I automated this as sendkeys, i know there is another option online, but I want the solution as sendkeys. Basically select Data>F2>F9>Enter. This works fine if i do it manually.

My VBa code looks like this

ActiveChart.ChartArea.Select
Application.SendKeys ("^{s}")
ActiveChart.SeriesCollection(2).Select
Application.SendKeys ("{f2}"), True
Application.SendKeys ("{f9}"), True
Application.SendKeys ("{f9}"), True
Application.SendKeys ("~"), True
Application.SendKeys ("^{s}")

ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
Application.SendKeys ("{f2}"), True
Application.SendKeys ("{f9}"), True
Application.SendKeys ("~"), True

Yet, one half of the 2nd set of data is still linked.

Thank you for your help in advance - LGR

Solution

  • You can do something like this to convert the Series.Values from a reference to a Range, to the actual underlying values.

    Sub foo()
Dim cht As Chart
Dim srs As Series

Set cht = ActiveChart

For Each srs In cht.SeriesCollection

    srs.Values = srs.Values

Next

End Sub