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

 

[billheath]

SKIP, You said something about updating the VBA for me. Have you done that? You're probably like me - I didn't work over the holidays!
Thanks again for all your help. Really wish I had your knowledge and experience. Bill

 

[SkipVought]

I literally just finished a few minutes ago.

Here are the modifications I made on the Daily Totals WorksheetChange event.

The SeriesNames range and the SeriesYValues ranges are both computed on the fly and both key off the "SeriesNames" header cell.

The SeriesNames range will include any cell in column A with a value below the SeriesNames header.

The chart Vertical axis Min and Max values are calculated on the fly to maximize the series variations. You may want to round up or down on the Max or Min values respectively.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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 has quit workin. The graph comes up but has laost the neccessary data.
Thanks

 

[billheath]

This is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'simple example of
' dynamically positioning chart and
' assigning values to a single series chart
'you must have a defined range of series names
' and a defined range for the columns containing the Values
'NOTE: Use the Watch Window or Object Browser to discover Object Properties

Dim iColCount As Integer

With Target.Parent.ChartObjects(1) 'the Target's Parent is the Worksheet
If Not Intersect(Target, [seriesnames]) Is Nothing Then 'only selection in the range
If Target.Count = 1 Then 'only if ONE cell selected
.Visible = True

With .Chart.ChartArea 'position chart
.Top = Target.Top + 30
.Left = Target.Left + Target.Width
End With

With .Chart.SeriesCollection(1) 'assign SeriesCol properties
iColCount = Target.Parent.UsedRange.Columns.Count
.Values = Intersect(Target.EntireRow, Range(Cells(1, 2), Cells(1, iColCount)).EntireColumn)
.Name = Target.Value
End With
End If
Else 'chart not visible
.Visible = False
End If
End With
End Sub

 

[SkipVought]

Plz be more specific. It looks good to me on sheet Daily Totals.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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]

bill,

I used my copy of your workbook from 27 Dec 21 19:15.

But if I paste in your code, I get a strange data shift.

Your code modification does not look at min and max values: mine does.

My code, which works perfectly on this workbook, changes the chart y-axis min & max to display the discrete y-values.

So why not tell us what you changed and why.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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]

Hey, Bill, did you get this sorted out?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!