Written by Allen Wyatt (last updated April 13, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Supriyo asked if there is a mouse event handler in VBA. He wants a value inserted in a cell when that cell is clicked on.
The standard way to do this is with the SelectionChange event. Every time the selection changes in the worksheet, the event is triggered. The event doesn't just trigger when a cell is clicked on, but also if someone presses a cursor control key that results in a different cell being selected.
As an example, let's say that you wanted cell B5 to contain the value 10 whenever that cell is selected. To implement that, you could use the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B5")) Is Nothing Then _ Range("B5").Value = 10 End Sub
This code is added to one of the sheet objects in the Project Explorer area of the VB Editor. Double-click the worksheet you want the event handler to apply to, and then add the macro to the resulting code window.
When the SelectionChange event is triggered, the target (the cell range being selected) is passed to the handler. The macro then checks to see if the target range contains cell B5, and if it does, stuffs the value 10 into cell B5. If you want to make sure that the macro only stuffs information into B5 if only B5 (the single cell) is selected, you can use this version of the macro:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = Range("B5").Address Then _ Range("B5").Value = 10 End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12514) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Mouse Click Event in VBA.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Strings are used quite frequently in macros. You may want to swap the contents of two string variables, and you can do so ...
Discover MoreWhen you enter information into a workbook, Excel automatically recalculates every worksheet in every open workbook on ...
Discover MorePlace a cell reference in a macro, modify the structure of your worksheet, and you may soon find that the cell reference ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-04-14 15:40:06
J. Woolley
"Supriyo asked if there is a mouse event handler in VBA. He wants a value inserted in a cell when that cell is clicked on." But he didn't specify the type of mouse click. There are two worksheet mouse click event handlers: Double-click and Right-click.
When a cell is double-clicked using the left mouse button it normally initiates Enter/Edit mode for typing into the cell. An alternate method is to select the cell (or left-click it) and press F2 (or simply start typing). If Supriyo wants cell B5 to contain the value 10 whenever that cell is double-clicked, he can add the following code to the applicable Sheet module:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
Range("B5").Value = 10
Cancel = True 'do not initiate Enter/Edit mode
End Sub
Right-clicking a cell normally opens its context menu. An alternate method is to select the cell (or left-click it) and press the Menu key (usually right of the Space bar). If Supriyo wants cell B5 to contain the value 10 whenever that cell is right-clicked, he can add the following code to the applicable Sheet module:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub
Range("B5").Value = 10
Cancel = True 'do not open the context menu
End Sub
It is not necessary to disable events when using these event handlers.
2024-04-13 09:40:08
Alex Blakenburg
Once you add Event Macros to your workbook, you will need to consider disabling and enabling Events when running macros eg without it in the case below the updating of B5 could very well trigger a Worksheet_Change event macro to run.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("B5").Address Then
Application.EnableEvents = False
Range("B5").Value = 10
Application.EnableEvents = True
End If
End Sub
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments