The worksheet object has a doubleclick and a rightclick event but strangely no click event. Instead, the worksheet has the SelectionChange event. Unfortunately this event won’t fire if we click the ActiveCell again. In this article I will show how to implement a true click event in a worksheet.
Single click
In this example we want to increase the value of a clicked cell by 1 modulo 6 (values 0-5).
Basically the workaround is simple: at the end of a Selection_Change move the ActiveCell to a neutral position, say [A1].
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveCell
.Value = (.Value + 1) Mod 6
End With
Application.EnableEvents = False
Me.Cells(1).Select
Application.EnableEvents = True
End Sub
After changing the value of the ActiveCell we select cell [A1]. To prevent that the SelectionChange event is fired again we need to disable events.
Note: if the user scrolls down a few pages and then clicks a cell, the sheet will scroll up automatically.
Double click
In this example we want a doubleclick to be processed as two single clicks.
Normally, if a user doubleclicks a cell it will enter edit mode. To prevent this we need to cancel the event.
Furthermore, a double click is always fired with and immediately after a SelectionChange. So, this happens after a doubleclick:
- SelectionChange handler:
- increase ActiveCell.Value by 1
- move ActiveCell to [A1]
- DoubleClick handler
- increase ??? by 1
At the moment the DoubleClick handler is being processed the ActiveCell is not anymore the cell that received the doubleclick!
The solution is simple: in every SelectionChange we store the ActiveCell address before we move it to [A1]. And in the DoubleClick handler we operate on the stored PreviousActiveCell, not on the ActiveCell.
Private mrPreviousActiveCell As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveCell
.Value = (.Value + 1) Mod 6
End With
Set mrPreviousActiveCell = ActiveCell
Application.EnableEvents = False
Me.Cells(1).Select
Application.EnableEvents = True
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With mrPreviousActiveCell
.Value = (.Value + 1) Mod 6
End With
Cancel = True
End Sub
Notice that since the ActiveCell is already moved to [A1] we don’t need to do that in the DoubleClick handler.
Right click
In this example we want to decrease the value of a right clicked cell by 1 modulo 6.
As before we have to cancel the rightclick event, this time to prevent the context menu to pop up. And as before a rightclick is always fired with and immediately after a SelectionChange.
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With mrPreviousActiveCell
.Value = (.Value - 2 + 6) Mod 6
End With
Cancel = True
End Sub
Explanation of the calculation: effectively we want to subtract 1 from the rightclicked cell value. Since the preceding SelectionChange added 1 we now have to subtract 2. And because the Mod operator doesn’t handle negative values as we want, we prevent them by adding the divisor once before we calculate the modulo.
A class implementation
The attached workbook implements the above solution in class CColoredCells. The class not only changes the value of a (right/double)clicked cell but also the color of interior and font. Furthermore, the class lets you define your own color palette, and the range of cells you want to color.
To initialize the class:
Set clsColoredCells = New CColoredCells ' create the object
With clsColoredCells
Set .Worksheet = Sheet1 ' the trapped sheet
Set .Range = Sheet1.Range("C3:F6, C8:F8, H3:H6") ' the range of cells to color
Set .Home = Sheet1.Range("H8") ' home position of activecell
Let .Colors = Array(lWHITE, lBLACK, lRED, lGREEN) ' the colors to use
End With
In the workbook you find this code in the module of Sheet1 (procedure Initialize).
Notes:
- You can set the Home cell wherever you want, but don’t let it intersect with Range;
- In the array you can define as many colors as you like;
- The class doesn’t handle any errors.
I hope this pixcel helps. Excel!