Private Sub Worksheet_SelectionCh 1

[billheath]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Union(Me.Range("SeriesNames"), Me.Range("SeriesYValues"))) Is Nothing Then
With Me.ChartObjects(1).Chart.SeriesCollection(1)
.Name = "=" & Intersect(ActiveCell.EntireRow, Me.Range("SeriesNames")).Address(, , , True)
.Values = Intersect(ActiveCell.EntireRow, Me.Range("SeriesYValues"))
End With
End If
End Sub
The above code is supposed to change a graph based on a set of numbers in the spreadsheet. However when I try to run it, I get the message "Message 'range' of object'_worksheet failed." I have the the series "Series Names" and "seriesYvalues" spelled out on the spreadsheet

 

[SkipVought]

Hi,

I get the message "Message 'range' of object'_worksheet failed."

…on what line of code?

Also replace, ActiveCell with Target.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[billheath]

The code stops on the 2nd line starting with: "If Not Intersect(Target, Union(Me.Range("SeriesNames"), Me.Range("SeriesYValues"))) Is Nothing Then"


I changed Active Cell to Target

 

[billheath]

Also, I defined the ranges within the excel sheet:
Name:Names!SeriesNames
Refers to:Names!$B$3:$B$6

Name:Names!SeriesYValues
Refers to:Names!$C3:$H3

 

[SkipVought]

Your SeriesYValues are in row 3. Why are you joining them to your SeriesNames in column B?

It appears that you intend to identify a cell that is in the area of the ROW of the cell selected in SeriesNames and some COLUMN of an anonymous cell in SeriesYValues???

Need a better explanation in words, not code, of what you're trying to accomplish.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

I think that this is what you may be looking for. CAVEAT: I'm not sure of the Chart.SeriesCollection syntax. I'm not at my PC.

Small edit…

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, [SeriesNames]) Is Nothing Then
      
      With Worksheets("Names").ChartObjects(1).Chart.SeriesCollection(1)
         .Values = Intersect(Target.EntireRow, [SeriesYValues].EntireColumn)
         .Name = Target.Value
      End With

   End If
   
End Sub

This code assumes that the Chart is always visible. You could tweek the code to make the chart not visible when any other selection is made and visible only when the selection coincides with SeriesNames.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Just thot I'd make a quick n dirty example of having…
[ul]
[li]A single chart[/li]
[li]Only visible on command[/li]
[li]Defined Range for command selection[/li]
[li]Command selection is Series Name[/li]
[li]Each series has a fixed number of values and a Defined Range for those columns in one row[/li]
[/ul]

The sheet design included…
[ul]
[li]Adding a Column Chart with one series[/li]
[li]Defining a Named Range in a row for the Columns containing Y Values[/li]
[li]Defining a Named Range in a column for the Series Names[/li]
[/ul]


There must be Range Name correspondence between…
[ul]
[li]SeriesNames on the sheet and SeriesNames in the code.[/li]
[li]SeriesYValues in the sheet and SeriesYValues in the code.[/li]
[/ul]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[billheath]

This works great until I have 73 rows. None of the rest show on the chart. Pardon my ignorance, but how do I change the series data?

 

[SkipVought]

Pease upload your current workbook that fails.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

...also...

In the Names Box, Select SeriesNames.

Does the selection include ALL the SeriesNames? That table is a Structured Table and this feature is designed to automatically expand SeriesNames rows as they are added to the table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[billheath]

The additional rows are working. The data points are in the columns based on date

 

[SkipVought]

I do not understand your problem then.

I need to see your workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[billheath]

How do I send it to you?

 

[billheath]

[URL unfurl="true"]https://res.cloudinary.com/engineering-com/raw/upload/v1640207891/tips/Crypto_values_to_send_wlqpoj.xlsm[/url]

 

[SkipVought]

Great. I glanced at it and I'll look in detail when I get back to my office.

I notice that the data is on sheet DAILY TOTALS, and that you have expanded the number of columns for SeriesYValues unannounced from 6 to 68. That would be part of the problem. I can fix that.

Do you want to display all 68 columns in the chart, with and without data?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[billheath]

If I can. I need to keep an ongoing record for me boss. Thanks

 

[SkipVought]

What we have here is a failure to communicate.

What happens in vagueness, stays in vagueness.

You have in your table 1) two empty rows, 2) a row of some sort of hard value Total and 3) an unnamed row based on the Total row--ALL in the table and then 4) a row BELOW the table column A value OMG, which is why you're not getting a chart for this outlier.

What happened in vagueness, is pretty obvious.

Please state clearly, concisely and completely what your requirement is. I could guess...AGAIN, but that would be foolish.

My suggestions:
1) Put your Total and following calculated row ABOVE your table where your users don't have to hunt for it and where it doesn't get in the way of an expanding table. Aggregations BELOW tables are a throwback to paper, pencils and adding machines! Aggregations belong where they can be easily accessed.
2) Put your wayward row IN the table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[billheath]

My goal is to have 2 excel tables. One for Stock quotes and one for crypto. I sent you the Crypto one.
We want to graph the values by date. When we select a stock or crypto symbol, We want to pop up a chart showing the prices by date.
It would be ideal to show dates instead of points (1 2 3 4 5,etc) across the bottom of the chart. However that is not absolutely necessary. The primary use of the charts is to determine the prices of stocks and crypto over time.

What we have now works for a limited number of entries. The number of SeriesNames seems to expand. However we need the chart to expand as we enter new data without a limit to the number of data points.

Hope this explains the problem. Your chart works great - just need to expand the number of entries per symbol.

 

[billheath]

Skip, Thanks so much for your help!
Bill

 

[SkipVought]

This is why I often instruct my users and here at Tek-Tips to...

State your requirements clearly, concisely and completely.

So in this case:
The Y-Axis values are in the Heading row of Table1 (where "SeriesNames" resides) in columns 2:LastColumn. You referred to "prices by date" but all you have in this row is 1.00, 2.00...24.00, 25, 26...68. I will modify the VBA accordingly.

What we have now works for a limited number of entries.

Not so...IF you put your entries IN the table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!