• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Pixcels.nl

  • Home
  • Categories
    • Excel apps
    • Pivottable stuff
    • PowerBI stuff
    • RGS en auditfiles
    • Sheet stuff
    • Userform stuff
    • VBA stuff
    • VBE stuff
    • XML stuff
  • About
You are here: Home / Categories / Sheet stuff / A true click event on worksheets

A true click event on worksheets

2013/02/28 by Frans

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.

TrueClick

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

Filed Under: Sheet stuff Tagged With: Events

Primary Sidebar

Recent posts

  • The Grand Totals range of a pivottable in Excel
  • Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • AuditfileValidator for Excel: validate and inspect Xml auditfiles
  • SAF-T auditfiles for test and development
  • Een postcodetabel op basis van BAG Extract 2.0

Recent comments

  • Harry Cuntapay on Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • Lucio on Disable Shift key on open
  • Lucio on Disable Shift key on open
  • Julius Peter on The sequence of events in workbooks
  • Ben on The sequence of events in workbooks

Categories

Tags

ActiveControl ActivePrinter ADCS auditfiles BAG Connection Custom tab Database Debug Direct Debit drill-through Events event trapping Focus IBAN ISO 21378 MDI MSXML Multiple Document Interface normalize PAIN.008 PivotTable postcode Power BI RGS Ribbon SAF-T schema SEPA SOM Table Userforms validate VBA XAF xml XPath

Copyright © 2023 · eleven40 Pro on Genesis Framework · WordPress · Log in