Excel - Trigger VBA Macro by Clicking on Standard Excel cell

[C Brandt]

First time poster

I would like to know if it is possible for the system to trigger the
execution of a VBA Macro with a simple click on an Excel Spread-sheet cell.



What I would like to accomplish is for the system to clear a series of
associated cells whenever I click on a cell to input a new value.



Any assistance would be greatly appreciated. A good on-line source for
tutorials of this nature would also be great.



Thanks in advance,



Craig

 

[JE McGimpsey]

First time poster

I would like to know if it is possible for the system to trigger the
execution of a VBA Macro with a simple click on an Excel Spread-sheet cell.

What I would like to accomplish is for the system to clear a series of
associated cells whenever I click on a cell to input a new value.

You can accomplish this using event macros:

http://www.mcgimpsey.com/excel/eventmacros.html

One way to do this by checking which cell was selected.
CTRL-/right-click the worksheet tab and choose View Code, then type or
paste this into the worksheet code that opens (delete any code that's
automatically put into the worksheet code module):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Address(False, False) = "A1" Then _
Range("B1:J1").ClearContents
End With
End Sub

Adjust your range references to suit.

Note that the SelectionChange event fires when a cell is clicked, but
also when tabbed into, selected using the arrow keys, etc. If that's not
what you want, I recommend instead using the BeforeDoubleClick event:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Target
If .Address(False, False) = "A1" Then
Range("B1:J1").ClearContents
End If
End With
End Sub