Run macro on cell click

[fatduck]

Hi there

I have a maco written which operates on a relative reference from a specific cell; Activecell H5 will run the macro on cells B5:G5, and H10 will run the macro on cells B10:G10, etc.

The only problem is that I don't know how to run the macro from the cell!

I have the text "Find" in the relevant cells in Column H, and I'd like the user to be able to click/double-click on one of these H cells to run the macro on that row.

Can anyone please help?

Thanks!
Daniel

 

[Gday Bruce]

Try this in the worksheet module, (substitute your macro for "MyMacro")

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 8 _
And Target.Value = "Find" Then
Call MyMacro
End If
End Sub

 

[Gday Bruce]

You'll probably want to feed the row variable to your macro so make the call as;

Call MyMacro(target.row)

 

[fatduck]

Mate, you are a legend, thank you!

 

[DonkeyOte]

I would suggest you Cancel the double click:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 8 And UCASE(Target.Value) = "FIND" Then
     Call MyMacro(Target.Row)
     Cancel = True
End If
End Sub

The only issue with this approach will be how user attempts to edit contents of H (if they are allowed to). If the contents of H are dynamic and determine the actions of your macro you may want to put an InputBox into the above such that end-user double clicks cell, enters text via InputBox (which in turn is used in Macro and text is set in active cell).

Without knowing your exact setup it's difficult to advise but just somethings to be aware of.

HTH

 

[fatduck]

Thanks for the helpful tips!