VBA – How to Change Merged cell in Excel with Events

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:

Enjoy it!

Tagged with: , , ,