Can Excel Record Mouse Coordinates/Clicks?

[Rufius]

I want to be able click on a bar of some kind (be it a jpg or some clip art or something) and generate a number based on where the line is clicked. So, perhaps, clicking to the far left of the line will generate a value of 00, and clicking to the far right will generate a value of 99.

The best method I have come up with is making 100 narrow columns, and having a bit of VBA code return values somewhere else based on what column the cell that was clicked was in. So, in this system, clicking on a cell in row C would generate a value of 02, and clicking on a cell in row Z would generate a value of 25. The problem with this approach, is that I haven't been able to figure out the VBA code to return a value after a click which is linked to the column of a cell.

any better ideas? I'm open to absolutely everything!

 

[AlphaFrog]

Insert an Image control from the Control Toolbox Toolbar. It's MouseDown event returns the x and y mouse click coordinates.

Put something like this in the worksheet's code module where X is the horizontal click coordinate.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Image1_MouseDown([COLOR=darkblue]ByVal[/COLOR] Button [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR], [COLOR=darkblue]ByVal[/COLOR] Shift [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR], [COLOR=darkblue]ByVal[/COLOR] X [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR], [COLOR=darkblue]ByVal[/COLOR] Y [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR])
    MsgBox X, , "Clicked: Horizontal Axis"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Rufius]

I am sorry for my ignorance, but it has been a good two years since I worked with VBA very extensively.

I got it to return a message in the message box, now how do I:
-set the min/max values
-assign the output to a cell instead of a messagebox

this is really great help!

 

[shg]

Private Sub Image1_MouseDown(ByVal Button As Integer, _
                             ByVal Shift As Integer, _
                             ByVal X As Single, _
                             ByVal Y As Single)
    Const iMin      As Long = 0
    Const iMax      As Long = 99

    Range("A1").Value = Int((iMax - iMin + 1) * X / Image1.Width) + iMin
End Sub

 

[AlphaFrog]

Private Sub Image1_MouseDown Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Range("A1").Value = X
End Sub

 

[Rufius]

IT WORKS!

WOW! this is very awesome!
This was one aspect of larger project that I was trying to figure out, and the help is very much appreciated!

is there a way to up the reputation for you guys or anything? I am very happy to have this figured out!
what is the process for marking this as solved?

 

[shg]

You have already made us feel puffed up with your thanks, nothing further necessary.

 

[AlphaFrog]

"Some day, and that day may never come, I will call upon you to do a service for me. But until that day, consider this justice a gift on my daughter's wedding day." - Don Corleone

Updated code for what it's worth...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Image1_MouseDown([color=darkblue]ByVal[/color] Button [color=darkblue]As[/color] [color=darkblue]Integer[/color], [color=darkblue]ByVal[/color] Shift [color=darkblue]As[/color] [color=darkblue]Integer[/color], [color=darkblue]ByVal[/color] X [color=darkblue]As[/color] [color=darkblue]Single[/color], [color=darkblue]ByVal[/color] Y [color=darkblue]As[/color] [color=darkblue]Single[/color])
    Range("A1").Value = Int(X / Image1.Width * 100) [color=green]'0 to 99[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Arch_Dev93]

Digging up an old topic here. I'm trying to do something similar to OP. Except, I want multiple rows of data. So i would want to click on the image, have it record in A1, and the next time I click, record in A2, and so on.

To go even further, I would ideally like a message prompt for each click that asks for some user input, such as "Left or Right" and record that in column B for each entry.

Is something like this possible?

Thanks!

 

[mightyMagnus]

Digging up an old topic here. I'm trying to do something similar to OP. Except, I want multiple rows of data. So i would want to click on the image, have it record in A1, and the next time I click, record in A2, and so on.

To go even further, I would ideally like a message prompt for each click that asks for some user input, such as "Left or Right" and record that in column B for each entry.

Is something like this possible?

Thanks!

For anyone who encounter similar problem to store the x and y values of Image Control MouseDown event, one get-around is to store the values returned from the MouseDown event into Public variables to be used in your code module.

• Create a Public variable in a standard module - Public variables are accessible to all modules

'Declare Public variable in your standard code module
Public rng As Range
Public scenario As Integer
Sub test()
    
    scenario = 2
    If scenario = 1 Then
        Set rng = Range("A1")
    ElseIf scenario = 2 Then
        Set rng = Range("B1")
    End If
    Do Until rng.Value <> vbNullString
        DoEvents
    Loop
End Sub

• In the MouseDown event of the image control (from the worksheet), store the data e.g. Button, Shift, X, Y into the public variable/object e.g. Range

Private Sub Image1_MouseDown(ByVal Button As Integer, _
                             ByVal Shift As Integer, _
                             ByVal X As Single, _
                             ByVal Y As Single)
    Select Case scenario
        Case 1
            rng.Value = x
            '...
        Case 2
            rng.Value = x
        '...
    End Select

End Sub