Hi Assaf –

When you use the MouseDown or MouseUp events, you can identify which point was clicked on, ignoring the issue of what is usually selected in the absence of chart event code.

To select one point on the first click, you can use this modification to the MouseUp procedure:

Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
        ByVal x As Long, ByVal y As Long)
 
    Dim ElementID As Long, Arg1 As Long, Arg2 As Long
    Dim myX As Variant, myY As Double
 
    With ActiveChart
        ' Pass x & y, return ElementID and Args
        .GetChartElement x, y, ElementID, Arg1, Arg2
 
        ' Did we click over a point or data label?
        If ElementID = xlSeries Or ElementID = xlDataLabel Then
            If Arg2 > 0 Then
                ' Select Point Arg2 of Series Arg1
                .SeriesCollection(Arg1).Points(Arg2).Select
            End If
        End If
    End With
End Sub

In general, when writing procedures to perform actions on Excel objects, it is not necessary to select those objects first. If this were my case, I would not the series and point, select the chart area, then do what I wanted with the point.

Hi Bao –

There are two possible solutions to this.

One approach is to keep the workbook open and use Application.OnTime to run the code that checks for an update. You could use a short duration (short compared to the update frequency), for instance every minute, and it would seem almost continuous.

The other approach is to make use of new capabilities of Microsoft Office and OneDrive called PowerApps. These would detect and respond to changes to files. I have not tried these yet, so I don’t know how smoothly they would interact with existing solutions.

Late on this post, but I wish I was back in my QA role from many years ago. This functionality would have made my life so much simpler.
I attended the Meetup launch and was impressed with the quality of the demo and the functionality.
Great stuff.

Bob

Hi Traci –

You cannot make this chart as a pivot chart, since it has some XY Scatter series, which are not supported in pivot charts. But you can Make Regular Charts from Pivot Tables. You will have to work out dynamic ranges for when the pivot table changes size (see Dynamic Chart using Pivot Table and Range Names.

Without seeing the data or knowing how you are pivoting or slicing the pivot table, it’s hard to know why the chart is losing details. Did you mean that the chart was losing the data labels which show the percentage changes? I know that there is a bug in data labels that use Values from Cells, where if you make a point disappear (maybe the series becomes shorter) then reappear (the series becomes longer again), the reappearing point has lost the labels.

-Traci

Chase sent me his workbook, and I discovered an issue with one of the series in the chart. The X values only had one cell:
=SERIES(“.01-.02”,Gauges!$DJ$2,Gauges!$DK$2:$DK$121,2)

When I changed the formula:
=SERIES(“.01-.02”,Gauges!$DJ$2:$DJ$121,Gauges!$DK$2:$DK$121,2)
the trendline calculator worked fine.

Chase –

I just tried this; my chart has 14 series, and I chose four of them to include in the regression. The SERIES formula of the combined series correctly includes the four chosen series, and the regression looks good.

Is it possible to share the workbook with the various charts?