Chart Values and X axis values from VBA

  • Thread starter Thread starter Kohai
  • Start date Start date
K

Kohai

Hi,

I'm creating a series of charts based on data in many
adjacent columns based on Name defined Ranges. However,
I have to hardcode the Workbook and Sheet names that are
used for the source names. My piece of code is:

With ChtObj.Chart.SeriesCollection(jbl)
.Values = "=Book3.xls!Sheet1_COL_" & ColAry(z)
.XValues = "=Book3.xls!Sheet1_Date"
End With

I want the code to handle the workbook & sheet names more
dynamically to accept a variable for wbk name or sheet
name (or number). I can't get any syntax to work.
Experts Help!

Thank you.

Kohai
 
Try this:

.Values = "=" & sBookName & "!" & sSheetName

or

.Values = "=" & sBookName & "!" & sRangeName

or better yet:

.Values = activesheet.range(sRangeName)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon

Thanks for that. I had checked your web site trying to
find an answer. The dilemma I have now is that the
active workbook name contains spaces, so I need to
incorporate the single quote marks. Those however are
for comments in VBA, so I'm not sure how to get around
that.
 
Keep the single quotes inside the double quotes, and Excel will not be
confused.

.Values = "='[" & sBookName & "]" & sSheetName & "'!" & sRangeName
.Values = "='" & sBookName & "'!" & sRangeName

I was wrong in the first line I provided. It should have been

.Values = "=[" & sBookName & "]" & sSheetName & "!" & sRangeName

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top