Here's a VBA tip on how to capture the Double Click event and do something useful with it.

• A Double Click event is simply an event that fires when you double click on a cell
• This example will demonstrate how to provide a simple interface to cycle through a series of values that enables a user of your Excel system to avoid entering things
• The advantage is that is is very quick and simple for a user

We have this:

We double click once, and get this:

We double click again, and get this:

We double click a third time, and go back to where we started.

Here's the code we'll use:

 Explanation

1. Run the StartDoubleClick procedure to start trapping double click events
2. Run StopDoubleClick to stop
3. MyDoubleClickMacro checks to see if you have double clicked on the cell we are interested in (C7).. if not, it does nothing
4. If you have double clicked on C7, it looks at the value of C7
5. If it's "Red", it changes it to green
6. If "Green", it changes to blue
7. In all other cases, it goes to red.

Note: Trapping double click has it's drawbacks as well in that if you double click on a cell, it no longer goes to Edit mode.

This code can be extended to check you're double clicking on the correct sheet as well, or modified to cope with double clicking on various cells, and handling the behaviour differently.

Download sheet to practise how to trap a Double Click event in Excel

Training Video on how to trap a Double Click event in Excel: