Create x,y points from mouse clicks

[ronjon65]

Is there a way to create a list of x,y points (say columns A and B) from a mouse click, but something that is on a grid (like a scatter plot). Basically, some way of showing a grid and then clicking points on it to make the x,y data.

Not that it needs to be done at this point, but ideally:

- a circle could show after the click
- a picture could be placed over top and then clicks could be performed over top of it (basically like sort of trace it with dots)
- snap to grid within a certain tolerance

Basically just looking for an overall idea, how to get started, and if this could even be done in Excel. Maybe I need something more like VB instead?

[p45cal]

Excel's probably not the ideal tool for this but it's possible.
As you asked, this will only suggest a way of getting started - and don't take it as authoritative.

In the attached I've set up an x,y scatter chart on its own chart sheet (this responds to events by default, an embedded chart in a stasndard worksheet doesn't but can be made to).
There's an event handler for mouse down (left-click) Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) which carries x and y values which are coordinates. Exactly what those coordinates refer to I'm not 100% sure, however the origin seems to be top left of the chart, so not the traditional DesCartes (cartesian) coordinate system. The ranges of returned values for x and y change according to the size of the chart on the screen, so it's a bit of a minefield.
I set up fixed chart x and y axis limits (0 to 100 for both), then in the event handler did a bit of arithmetic on the returned x and y values to try to bring them more into line with traditional x,y coordinatres (origin at bottom left) and to try to bring them within 0 to 100. The code adds a new series as a single point (it could have been a single point within an existing series). This works when the chart occupies about a third of my screen on the left.
If you click in the vicinity of the top left of the chart you're most likely to see the data point being plotted on the chart.

If you do an internet search for translating the x and y values returned in the event handler to useable chart coordinate I'm sure you'll find something useful, which will handle varying window sizes/screen resolutions.

If you have the attached open in a smallish window it might get somewhere close to behaving as you want.

The code in the attached is rudimentary:
Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
'Debug.Print x, y
With Me.SeriesCollection.NewSeries
.XValues = x / 6.2
.Values = (y * -1 + 400) / 3.6
End With
End Sub

[ronjon65]

Thanks, I tried something like this before, but had an issue with pasting a picture on a chart (doesn't want to resize, etc.)

I think something like this would be good enough though. Forget about the chart. Can you just have it populate the x,y position in columns A and B? That way I can just paste a picture in a sheet and trace over it with mouse clicks. I guess it needs a start/stop macro?

After that, I can write something that moves adjusts the coordinates (move and scale the object). That shouldn't be too difficult.

[p45cal]

something along the lines of:
Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
'Debug.Print x, y
With Sheets("Sheet1")
Set Destn = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
Destn.Resize(, 2).Value = Array(x, y)
End With
End Sub

[ronjon65]

Thanks again, I think this will work good enough. I can paste a picture on top of blank chart and just click the points. This creates my x,y and then I can perform math operations on that set.

One thing that would be nice is to just place an a circle object over top the mouseclick as well. Can that be done? And then obviously an operation to delete all the objects at a later point. That way I can put a picture of a grid on "Chart1" and make clicks on it. I can do that now, but you tend to get lost because you can't see the picked points.

[p45cal]

In the attached, click at various points in the cell range A1:L26.
It's an ActiveX image control with a transparent background, pushed up and left on the sheet.
Points (shapes) are put directly on the sheet (not the Image control).
You don't have to have the x/y coordinates recorded on the same sheet, of course.
You could change the column widths and row heights of the underlying sheet to create a makeshift grid.
If you're prepared for some work, you could use Shapes.BuildFreeform and .AddNodes to get an outline rather than just points.
You might find https://newtonexcelbach.com/2008/11/11/drawing-in-excel-7-creating-drawings-from-coordinates/ interesting.

[ronjon65]

Hey this is pretty cool. I like it a lot.

I had a some issue (still work in Excel 2003) and Microsoft broke that with an update in Dec 2014. So I tried it on another computer with a fresh 2003 install and it worked fine. I will have to see if there is an "official" fix for the Dec 2014 issue...so far a lot of random stuff.

Anyway, its looking pretty good. There might even be a way to lay a grid down and have it snap to the grid, but I think this will probably be good enough really.