The worksheet events which are built within VBA are quite useful. One may build them quite easily in Excel and they work satisfactory.
However, let’s imagine, that you have a merged cell as in the picture above and you want it to show the time, whenever you are clicking on it. In order to do this, you have to check the following:
- Start by disabling the events on the worksheet, because you would like to ignore the events, triggered by your event. Otherwise it will become an endless loop. With Application.EnableEvents = False;
- Check whether the cell you are interested in is within the MergedArea of any of the merged cells. This is achieved with If Not Intersect(Target.Cells(1), Range(“C4”).MergeArea Is Nothing Then;
- Select Case Target.Cells(1) gives a good option to get the value of the Merged Area;
- Change the value of the cell;
- At the end set the Application.EnableEvents = True;
- It is a good idea to use error handling here (although error handling is always a good idea), thus if something breaks during the execution, the Events are reset back to True;
Here comes the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) On Error GoTo Worksheet_SelectionChange_Error Application.EnableEvents = False Debug.Print Target.Cells.Count If Not Intersect(Target.Cells(1), Range("C5").MergeArea) Is Nothing Then Select Case Target.Cells(1) Case vbNullString Target.Cells(1) = Now Case Else Target.Cells(1) = vbNullString End Select End If Range("A1").Select Application.EnableEvents = True On Error GoTo 0 Exit Sub Worksheet_SelectionChange_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ")" Application.EnableEvents = True End Sub |
Enjoy it!