Dynamic Data Source for Chart uses Workbook name - needs to use a variable

[jimrood]

I have an Excel 2010 file that contains a chart that uses an ActiveX combo box to select the data series based on a named range in another worksheet. I was having difficulty with the chart replacing the named range with the static cell reference range. My solution was to run a macro on change. (See Code below)

Private Sub OpenInvEntity_Change()​

​

ActiveSheet.Unprotect​

​

ActiveSheet.ChartObjects("OpenInv_Graph").Activate​

ActiveChart.SeriesCollection(1).Values = _​

"=CPI_Trend_Data.xlsm!OpenInvDataSeries"​

ActiveChart.SeriesCollection(1).XValues = _​

"=CPI_Trend_Data.xlsm!OpenInvDateRange"​

​

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True​

​

End Sub
​

The code works fine when run locally. However, I am uploading the file onto our intranet. When users click on a link to gain access to the file they are prompted with a choice to save or open the file. If they choose open, the workbook name reverts to View.aspx and the macro will no longer work.

I've been attempting to define an object variable using ThisWorkbook or even ActiveWorkbook but have had no luck.

 

[Andrew Poulsom]

Did you try eg?

ActiveChart.SeriesCollection(1).Values = _
"='" & ThisWorkbook.Name & "'!OpenInvDataSeries"

I must admit I don't understand the reverting to View.aspx though.

 

[jimrood]

Did you try eg?

ActiveChart.SeriesCollection(1).Values = _
"='" & ThisWorkbook.Name & "'!OpenInvDataSeries"

I must admit I don't understand the reverting to View.aspx though.

That worked like a charm! Thanks so much. Not sure I can explain why the file name reverts to View.aspx either. I'm using a locally developed application that allows a user to upload a file to our server and it generates a link that can be copied and pasted into an email in lieu of an attachment. The app also has a problem with spaces in file names and inserts _ instead.

Once again thanks!