Excel keyboard ability to change a value in a displayed dynamic chart range line

Question

I plan to have a chart plotted line consisting of 20 values set up as a dynamic chart range. I then can manually go into the sheet and update these values one at a time and the chart will change its display.

But instead of manually changing the sheet values, I want to be able to; 1. Move the cursor to one of the displayed points on the line [using mouse?]. 2. Use the up/down arrow keys to change the value of the point [to the value of the y axis where the point is moved to]. The incremental value the point is moved up/down with the arrow keys can be a tunable value in the sheet. 3. As the point value changes, the line should re-plot using the latest updated 20 values in the range.

Thank you.

Don

Tried nothing yet. Just an idea I want to use in possible applications.

No code for this change.

The problem summary details the results.

P.S. Since I got a -1 vote, I thought I should add more about what this would be used for in real life.

I have Macular Degeneration, but slowed it down from getting worse many years ago, and get regular shots in the one bad eye. Over the years, the original Amsler Grid has been manually used by patients to check for a deterioration in the condition, so they can quickly see their Eye Doc to get a shot before it gets worse. Lately, machines have been built that can test for the condition worsening, and report it to the user and/or their doctor. However, these machines are expensive. And the Amsler grid results are very subjective.

My idea is to develop an Excel sheet with chart to replace the Amsler Grid with one straight line that can be shown at any angle through a center point and allow anyone with Excel to test their eyes with this dynamic replacement. The Amsler grid has a Dot in the center and a bunch of horizontal and vertical grid lines. You cover one eye and with the other, look at the centered dot and then subjectively determine if you see the grid lines perfectly, of if they appear distorted.

What I want to do is to have a chart on which the user can selectively spot any distorted portions of the line, click on a distorted spot on the line, then "move" the spot to line up with the users view of the rest of the line. After the user has "straightened" out the line, he submits it. The logic then inverts the corrected line [which in reality is inversely distorted by the users movements of points on the line] and then this line is sent to the doctor involved via email. The doc can then determine how bad the distortion is by comparing it to past emailed lines from the same user and comparisons to the perfect straight line the user started with, and an appointment can be made for an office visit.

I can handle the best fit quadratic logic for displaying the line through the points, starting with linear, then fitting to what the users distorted results are. I just need help on the originally stated questions.

Answer 1

So here is my take on this (maybe there are more elegant ways), it's oversimplyfied to what you want I guess and not all your questions get answered, but maybe you'll get to implement some of it.

1) Create a chart on it's own seperate sheet, my example data on a line graph:

2) Assign code to Private Sub Chart_Activate and Private Sub Chart_Deactivate, for example:

Private Sub Chart_Activate()

Application.OnKey "{UP}", "GoUp"
Application.OnKey "{DOWN}", "GoDown"

End Sub

Private Sub Chart_Deactivate()

Application.OnKey "{UP}", ""
Application.OnKey "{DOWN}", ""

End Sub

3) The OnKey will activate two modules called GoUp or GoDown, I have written them as follows (maybe it can be done better)

Sub GoUp()

If TypeName(Selection) = "Point" Then
    For Each pt In ActiveChart.SeriesCollection(1).Points
        x = x + 1
        If pt.Name = Selection.Name Then
            With ThisWorkbook.Sheets("Sheet1")
                .Range("A" & x).Value = .Range("A" & x).Value + 1
            End With
            Exit For
        End If
    Next pt
End If

End Sub

Sub GoDown()

If TypeName(Selection) = "Point" Then
    For Each pt In ActiveChart.SeriesCollection(1).Points
        x = x + 1
        If pt.Name = Selection.Name Then
            With ThisWorkbook.Sheets("Sheet1")
                .Cells(x, 1) = .Cells(x, 1) - 1
            End With
            Exit For
        End If
    Next pt
End If

End Sub

Change the +1 and -1 to an assigned variable if need be.

4) Result on pressing arrow down or up when a point is selected: