In Excel, How do I trap a mouse click in a specific cell to activate a macro?

Question

I have a Excel macro that does a simple sort. I want the end-user to be able to click on a specific call and initiate the macro. I need to trap the mouse-click to respond on it.

Answer 1

To prompt the user to click a range

You want to use an inputbox

Set myRange = Application.InputBox(prompt := "Sample", type := 8)

To start a macro when a cell is selected

Or, if that's not what you meant and you meant activate the macro on the click you need a worksheet_change event

This goes on the sheet's VBA in the VBE. Something like

Private Sub Worksheet_Change(ByVal Target as Range)
if not intersect(target,range("A1")) is nothing then
 'call macro
end if
End sub

Other Ways

You can also put a button or control into your sheet that triggers a macro to begin when it is clicked. Take a look at the microsoft KB