使用鼠标按钮更改单元格值-Excel [英] Changing cell value with mouse buttons - Excel

问题描述

我正在尝试使用鼠标按钮更改单元格的值. 我想做的是这样:

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

如果左键单击某个范围的单元格:F3至F500和G3至G500.单元格中有一个数值.假设7.加1.因此，如果我左键单击具有值7的F4，该值将变为8.如果我右键单击该单元格，它将减去.单元格(7)的值变为6.

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.

我对VBA非常陌生，甚至不知道如何开始.所以我的问题是:我该如何做?如果这个问题不够具体.如何在这些单元格上注册鼠标单击?

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?

推荐答案

因此，经过一番尝试之后，我使这段代码起作用了:

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

您必须将此代码粘贴到工作表的 code部分(最可能是Visual Basic视图中"Microsoft Excel Objects"下的"Sheet1").它不能在其他模块中工作，而只能在特定的工作表中工作.您还必须将以下代码添加到常规模块(最有可能是"Module1"):

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

它可以帮助您了解此代码的作用:始终在工作表中进行选择时调用 SelectionChange ，在单击鼠标左键，单击鼠标右键，使用箭头按钮等方面没有区别.通过键状态功能，我们可以专门检查左键或右键.

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.

选定的单元格始终切换到左侧的同一行，因为否则Excel将无法检测到另一个左键单击(没有特定事件用于左键单击，并且同一单元格不会触发选择更改.

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).

这篇关于使用鼠标按钮更改单元格值-Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！