Well, for this purpose there're worksheet events. Although there's no special event for a left click, you can (mis)use the
SelectionChangeevent, you just have to make sure that each left click results in a change of the selection.
I usually hide my selection somewhere in the upper left corner, and set it back there after each selection change. So selecting a cell (apart from the one where I park my selection) always causes a SelectionChange event, even if I select the same cell that I might have selected right before.
The coding for the worksheet module looks like this:
Catching the right mouse click is even easier because there's an event dedicated to this:
Unfortunately there's another little obstacle to overcome: When you right click in a cell, this also causes a SelectionChange, so the macro
Worksheet_SelectionChangewill be executed, and unfortunately it will be executed before
So if you don't want the code for left clicks run in case of right click, you need to find out if a left click or a right click caused the SelectionChange.
VBA doesn't have any means to do it, but there're quite a few useful Windos APIs, and one can tell you for every button if it's pressed down at the moment:
With the following function (put it in a normal code module) you can check if a key on the keyboard or a mouse button is pressed or not:
Now you can insert at the beginning of
Worksheet_SelectionChangethe following line:
Worksheet_SelectionChangefrom doing anything in case of a right click.
Another thing I'd like to mention is the fact that it can be a little bit inconvenient to have your selection go away every time you change it. For instance, in order to enter something in a cell, you'll need this cell to stay selected.
I usually place a Togglebutton on my worksheets so I can easily switch between the state where clicking in a cell automatically makes a macro run and then the selection goes back again, and a state where this won't happen.
Let's assume the Togglebutton has the name
Togglebutton1, then the entire coding looks like this:
And one last remark:
Each key and mouse button has a unique number assigned, which is what is passed to the function KeyPressed and further on to the API. Of course it's tedious to remember all key codes, and makes reading code that uses just numbers unnecessarily difficult. So VBA has defined constants for each key that can be used instead, and you can find their names by going to your Visual Basic Editor, into the Object Browser, and looking for class KeyCodeConstants in library VBA.
Or you can just place your cursor on such a constant name in your code and select View → Definition