Excel 2019

Worksheet BeforeDoubleClick and BeforeRightClick Events

In this tutorial, we’ll discuss the BeforeDoubleClick and BeforeRightClick worksheet events. The Worksheet_BeforeDoubleClick event occurs when the user double-clicks a cell and the Worksheet_BeforeRightClick event occurs when the user right-clicks a worksheet cell.

As we discussed earlier, each worksheet (sheet object) has a code window to handle events. You can quickly access that code window by right-clicking the worksheet’s tab and selecting the View Code:

Worksheet view code window

Worksheet_BeforeDoubleClick Event Procedure

Excel detects when a cell is double-clicked and fires the Worksheet_BeforeDoubleClick event. This event doesn’t occur when the user double-clicks the border of a cell. The Worksheet_BeforeDoubleClick event procedure has two parameters, Target and Cancel:

  • Target
    It is the Range object which represents the cell that was double-clicked.
  • Cancel
    By default, double-clicking a cell puts it into edit mode. You can halt this default behavior by assigning the True value to the Edit argument.

Disabling default behavior of the double click:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Cancel = True
End Sub

Displaying the address of the double-clicked cell:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 MsgBox (Target.Address)
End Sub

In the following example, double-clicking a cell toggles the font-weight. If Normal, it makes it Bold. If the font is bold, it applies the Normal font-weight:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Target.Font.Bold Then
  Target.Font.Bold = False
 Else
  Target.Font.Bold = True
 End If
 'Prevent default double-click action
 Cancel = True
End Sub

You can write the above code in more simplest and readable form:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Target.Font.Bold = Not Target.Font.Bold
 Cancel = True
End Sub

Worksheet_BeforeRightClick Event Procedure

The BeforeRightClick event is similar to the BeforeDoubleClick event except that it consists of right-clicking a cell. Excel displays a shortcut menu when the user right-clicks in a worksheet. Before the shortcut menu displayed, the Worksheet_BeforeRightClick event occurs. You can use this event to disable the shortcut menu, customize it (by adding or removing the menu items in it) or format the cell before displays to the user.

The Worksheet_BeforeRightClick event procedure has two parameters, Target and Cancel:

  • Target
    It is the Range object which represents the cell that was double-clicked.
  • Cancel
    By default, right-clicking a cell pop-ups the shortcut menu. You can halt this default behavior by assigning the True value to the Edit argument.

Disabling default behavior of the right click:

Private Sub Worksheet_BeforeRightClick (ByVal Target As Range, Cancel As Boolean)
 Cancel = True
End Sub

Displaying the address of the right-clicked cell:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
 MsgBox (Target.Address)
 Cancel = True
End Sub

In the following example, right-clicking a cell toggles the font-weight. If Normal, it makes it Bold. If the font is bold, it applies the Normal font-weight:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
 Target.Font.Bold = Not Target.Font.Bold
 Cancel = True
End Sub
Advertisement:
Advertisement: