action when a cell is clicked

Question

H! I am new to VBA. This maybe a too simple of a question, but I am struggling to use VBA: when a cell(1,1) is clicked as it has 1, the msgbox would show up saying "hi"

Sub test()
  'click action 'when cell(1,1) is clicked and if cells(1,1) is 1, msgbox saying "hi" will show up, if not nothing If Cells(1, 1) = 1 Then
  MsgBox "hi"
  Else 
End If
End Sub

Answer 1

The challenge for me was to distinguish between a "move on the cell via cursor movement" and "cell clicked by mouse". The Worksheet_SelectionChange() does not do this. I found something here (also late, but maybe still valuable).

In short, add the following code in the VBA editor to the concerning sheet:

Option Explicit
 
Private Type POINTAPI
    x As Long
    y As Long
End Type
 
Private Type MSG
    hwnd As Long
    Message As Long
    wParam As Long
    lParam As Long
    time As Long
    pt As POINTAPI
End Type
 
Private Declare Function PeekMessage Lib "user32" _
Alias "PeekMessageA" _
(ByRef lpMsg As MSG, ByVal hwnd As Long, _
ByVal wMsgFilterMin As Long, _
ByVal wMsgFilterMax As Long, _
ByVal wRemoveMsg As Long) As Long
 
Private Const PM_NOREMOVE = &H0
Private Const PM_QS_INPUT = &H20000

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    Dim Message As MSG
 
    'check for left-mouse button clicks.
    PeekMessage Message, 0, 0, 0, PM_NOREMOVE Or PM_QS_INPUT
    'if left-mouse clicked on cell F4 run macro
    If Message.Message = 512 Then
        If Selection.Address = Range("f4").Address Then
            MsgBox "You clicked cell: " & Selection.Address
        End If
    End If
 
End Sub

How it works: If the cell changed (however), the next message concerning mouse movement is queried. If it is 512 (WM_MOUSEMOVE) and the the position matches the interesting cell (in this case F4) the messagebox is displayed.

---

If you get an error about 64bit compatibility and the use of PtrSafe, you can enter the following (replace the definitions above):

Private Type MSG
    hwnd As LongLong
    Message As Long
    wParam As LongLong
    lParam As LongLong
    time As Long
    pt As POINTAPI
End Type

Private Declare PtrSafe Function PeekMessage Lib "user32" _
Alias "PeekMessageA" _
(ByRef lpMsg As MSG, ByVal hwnd As LongLong, _
ByVal wMsgFilterMin As Long, _
ByVal wMsgFilterMax As Long, _
ByVal wRemoveMsg As Long) As Long