A true click event on worksheets

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:

1. SelectionChange handler:
   • increase ActiveCell.Value by 1
   • move ActiveCell to [A1]
2. 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!

Download

TrueClick-v1.xlsm