2

I am trying to change the value of a cell with the mouse buttons. What I want to do is this:

If you left click on a certain range of cells: F3 to F500 and G3 to G500. And there is a numeric value in the cell. Let's say 7. Add 1. So if I left click on F4 that has the value 7. The value becomes 8. And if I right click that cell it subtracts. the value of the cell (7) becomes 6.

I am very new to VBA and I do not even know how to start. So my question is: How do I do the above? And if that question is not specific enough. How do I register the mouse clicks on those cells?

user3047395
  • 63
  • 2
  • 9
  • http://www.mrexcel.com/forum/excel-questions/278381-visual-basic-applications-can-visual-basic-applications-recognise-mouse-click-event.html – enderland May 09 '14 at 12:03

2 Answers2

1

So, after a bit of trying I got this code to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Intersection
Dim Rng As Range
Set Rng = Range(Cells(3, 6), Cells(500, 7))

Set Intersection = Application.Intersect(Target, Rng)
If Not Intersection Is Nothing Then
    If IsNumeric(Selection.Value) And Selection.Value <> "" Then
        If (GetAsyncKeyState(vbKeyRButton)) Then 'right mouse button
            Selection.Value = (Selection.Value - 1)
        ElseIf (GetAsyncKeyState(vbKeyLButton)) Then 'left mouse button
            Selection.Value = (Selection.Value + 1)
        End If
        Cells(Selection.Row, 1).Select
    End If
End If

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

You have to paste this code into the code section of the worksheet (most probably "Sheet1" under "Microsoft Excel Objects" in the Visual Basic View). It won't work in other modules but the specific worksheet one. You also have to add the following code to a regular module (most probably "Module1"):

Public Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

It may help you see what this code does: SelectionChange is always called upon a selection in the worksheet, not differing between left click, right click, arrow buttons etc. The Key State function allows us to check specifically for left or right click.

The selected cell is always switched to the same row on the left, because Excel wouldn't be able to detect another left click otherwise (there is no specific event for the left click and the same cell wouldn't trigger SelectionChange again).

EngJon
  • 987
  • 8
  • 20
  • NICE CODE! +1 ..............Consider adding a range test to the **BeforeRightClick** event routine to allow normal right-behavior outside of the **F:G** area. – Gary's Student May 09 '14 at 13:33
  • Thanks. I was thinking about this range test as well. It would be a mirror of the check in **SelectionChange** and may be a good exercise for someone new to VBA. – EngJon May 09 '14 at 13:46
  • Thanks! I understand the code. But I get an error. When I paste the code 'Public Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer' in Module1. I get the error: The code must be updated for use on 64-bit systems. How do you fix that? – user3047395 May 09 '14 at 15:29
  • I fixed it. Don't know if it is the correct answer. But I put PtrSafe before Function and now it works. – user3047395 May 09 '14 at 15:47
  • Still 1 problem. When I click a cell with my right mouse button. It works perfectly. But if I click it with my left mouse button, it does not work. And I cannot figure out what the problem is... – user3047395 May 09 '14 at 15:57
  • http://support.microsoft.com/kb/983043 this suggests that you should simply ignore the compile error and just run the macro as it was before. Maybe this change to the code leads to the function not working properly anymore. – EngJon May 09 '14 at 22:39
0

This code uses double-click. Enter the following event macro in the worksheet code area:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim FG As Range
    Set FG = Range("F3:G500")
    If Intersect(Target, FG) Is Nothing Then Exit Sub
    Cancel = True
    Target.Value = Target.Value + 1
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Gary's Student
  • 95,722
  • 10
  • 59
  • 99