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