OnClick在Excel VBA [英] OnClick in Excel VBA

问题描述

有没有办法在Excel中单击VBA中的单元格？我不是指$ code> Worksheet_SelectionChange 事件，因为如果单元被多次点击，则不会触发多次。 BeforeDoubleClick 也不能解决我的问题，因为我不想要用户双重点击。

我当前的解决方案使用 SelectionChange 事件，但它似乎需要使用全局变量和其他次优的编码实践。这也似乎很容易出错。

解决方案

显然，没有完美的答案。但是，如果要允许用户

1. 选择某些单元格


2. 允许用户更改那些单元格
   和


3. 会陷入每次点击，甚至在同一单元格上重复点击
   

那么最简单的方法似乎是将焦点移动到所选单元格上，以便点击它将触发Select事件。

选项是按照我上面提到的方式移动焦点，但这可以防止单元格编辑。另一个选择是将选择扩展为一个单元格（左/右/上/下），因为这允许编辑原始单元格，但是如果单独的单元格被单独单击，则会触发Select事件。

如果您只想捕获单列单元格的选择，则可以在右侧插入一个隐藏列，扩展选择以将隐藏单元格包含在右侧当用户单击时，这会给您一个可编辑的单元格，每次单击它时都可以被捕获。代码如下

  Private Sub Worksheet_SelectionChange（ByVal Target As Range）
'prevent当我们再次选择事件触发将选项扩展到
 Application.EnableEvents = False 
 Target.Resize（1，2）。选择
 Application.EnableEvents = True 
 End Sub 
  

Is there a way to catch a click on a cell in VBA with Excel? I am not referring to the Worksheet_SelectionChange event, as that will not trigger multiple times if the cell is clicked multiple times. BeforeDoubleClick does not solve my problem either, as I do not want to require the user to double click that requently.

My current solution does work with the SelectionChange event, but it appears to require the use of global variables and other suboptimal coding practices. It also seems prone to error.

解决方案

Clearly, there is no perfect answer. However, if you want to allow the user to

1. select certain cells
2. allow them to change those cells, and
3. trap each click,even repeated clicks on the same cell,

then the easiest way seems to be to move the focus off the selected cell, so that clicking it will trigger a Select event.

One option is to move the focus as I suggested above, but this prevents cell editing. Another option is to extend the selection by one cell (left/right/up/down),because this permits editing of the original cell, but will trigger a Select event if that cell is clicked again on its own.

If you only wanted to trap selection of a single column of cells, you could insert a hidden column to the right, extend the selection to include the hidden cell to the right when the user clicked,and this gives you an editable cell which can be trapped every time it is clicked. The code is as follows

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'prevent Select event triggering again when we extend the selection below
  Application.EnableEvents = False
  Target.Resize(1, 2).Select
  Application.EnableEvents = True
End Sub

这篇关于OnClick在Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！