Left click or Right click, that's the question
So you want to run some code when clicking in a cell, and you want to run something else when right clicking into this cell?
Well, for this purpose there're worksheet events. Although there's no special event for a left click, you can (mis)use the SelectionChange event, 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:
'Actions for SelectionChange
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'if A1 was selected, then exit to avoid another SelectionChange event
If ActiveCell.Address = "$A$1" Then Exit Sub
'do here whatever should be done in case of a selection change
'...
'now set the selection back again
Range("$A$1").Select
End Sub
Catching the right mouse click is even easier because there's an event dedicated to this: BeforeRightClick
'Actions for right click
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'set cancel to keep Excel's context menu from appearing
Cancel = True
'now do here whatever should be done in case of a right click
'...
'and set the selection back again
Range("$A$1").Select
End Sub
Unfortunately there's another little obstacle to overcome: When you right click in a cell, this also causes a SelectionChange, so the macro Worksheet_SelectionChange will be executed, and unfortunately it will be executed before Worksheet_BeforeRightClick.
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: GetAsyncKeyState
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:
'The declaration tells VBA where to find and how to call the API
Private Declare Function GetAsyncKeyState Lib "user32" _
(ByVal vKey As Long) As Integer
'The function returns whether a key is pressed or not
Public Function KeyPressed(ByVal Key As Long) As Boolean
KeyPressed = CBool((GetAsyncKeyState(Key) And &H8000) = &H8000)
End Function
Now you can insert at the beginning of Worksheet_SelectionChange the following line:
If KeyPressed(vbKeyRButton) = True Then Exit Subto keep Worksheet_SelectionChange from 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:
'************************************'
'* Coding for the worksheet section *'
'************************************'
'Actions for SelectionChange
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Don't react to SelectionChange when right click
If KeyPressed(vbKeyRButton) = True Then Exit Sub
'if A1 was selected, then exit to avoid another SelectionChange event
If ActiveCell.Address = "$A$1" Then Exit Sub
'only run my left click coding if the togglebutton is pressed
If Togglebutton1.Value = False Then Exit Sub
'do here whatever should be done in case of a selection change
'...
'now set the selection back again
Range("$A$1").Select
End Sub
'Actions for right click
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'only run my right click coding if the togglebutton is pressed
If Togglebutton1.Value = False Then Exit Sub
'set cancel to keep Excel's context menu from appearing
Cancel = True
'now do here whatever should be done in case of a right click
'...
'and set the selection back again
Range("$A$1").Select
End Sub
'**********************'
'* Coding in Module 1 *'
'**********************'
Option Explicit
'The declaration tells VBA where to find and how to call the API
Private Declare Function GetAsyncKeyState Lib "user32" _
(ByVal vKey As Long) As Integer
'The function returns whether a key is pressed or not
Public Function KeyPressed(ByVal Key As Long) As Boolean
KeyPressed = CBool((GetAsyncKeyState(Key) And &H8000) = &H8000)
End Function
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
Well, for this purpose there're worksheet events. Although there's no special event for a left click, you can (mis)use the SelectionChange event, 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:
'Actions for SelectionChange
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'if A1 was selected, then exit to avoid another SelectionChange event
If ActiveCell.Address = "$A$1" Then Exit Sub
'do here whatever should be done in case of a selection change
'...
'now set the selection back again
Range("$A$1").Select
End Sub
Catching the right mouse click is even easier because there's an event dedicated to this: BeforeRightClick
'Actions for right click
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'set cancel to keep Excel's context menu from appearing
Cancel = True
'now do here whatever should be done in case of a right click
'...
'and set the selection back again
Range("$A$1").Select
End Sub
Unfortunately there's another little obstacle to overcome: When you right click in a cell, this also causes a SelectionChange, so the macro Worksheet_SelectionChange will be executed, and unfortunately it will be executed before Worksheet_BeforeRightClick.
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: GetAsyncKeyState
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:
'The declaration tells VBA where to find and how to call the API
Private Declare Function GetAsyncKeyState Lib "user32" _
(ByVal vKey As Long) As Integer
'The function returns whether a key is pressed or not
Public Function KeyPressed(ByVal Key As Long) As Boolean
KeyPressed = CBool((GetAsyncKeyState(Key) And &H8000) = &H8000)
End Function
Now you can insert at the beginning of Worksheet_SelectionChange the following line:
If KeyPressed(vbKeyRButton) = True Then Exit Subto keep Worksheet_SelectionChange from 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:
'************************************'
'* Coding for the worksheet section *'
'************************************'
'Actions for SelectionChange
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Don't react to SelectionChange when right click
If KeyPressed(vbKeyRButton) = True Then Exit Sub
'if A1 was selected, then exit to avoid another SelectionChange event
If ActiveCell.Address = "$A$1" Then Exit Sub
'only run my left click coding if the togglebutton is pressed
If Togglebutton1.Value = False Then Exit Sub
'do here whatever should be done in case of a selection change
'...
'now set the selection back again
Range("$A$1").Select
End Sub
'Actions for right click
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'only run my right click coding if the togglebutton is pressed
If Togglebutton1.Value = False Then Exit Sub
'set cancel to keep Excel's context menu from appearing
Cancel = True
'now do here whatever should be done in case of a right click
'...
'and set the selection back again
Range("$A$1").Select
End Sub
'**********************'
'* Coding in Module 1 *'
'**********************'
Option Explicit
'The declaration tells VBA where to find and how to call the API
Private Declare Function GetAsyncKeyState Lib "user32" _
(ByVal vKey As Long) As Integer
'The function returns whether a key is pressed or not
Public Function KeyPressed(ByVal Key As Long) As Boolean
KeyPressed = CBool((GetAsyncKeyState(Key) And &H8000) = &H8000)
End Function
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