How can I assign a macro to a cell with text in it, using the right mouse click or any other method?

[TedX]

Hi, how can I have a word written in a cell, assigned to a macro, I believe one way to do it is via using the before right mouse click action on your worksheet? Can anyone show me the way? For the purpose of this request, please consider the word 'Next' will be written in cell "U5" and the macro name is 'top3wa' Thanks in advance for any assistance.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Next" in U5 and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "U5" Then Exit Sub
    Application.ScreenUpdating = False
    If Target = "Next" Then
        Call top3wa
    End If
    Application.ScreenUpdating = True
End Sub

 

[TedX]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Next" in U5 and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "U5" Then Exit Sub
    Application.ScreenUpdating = False
    If Target = "Next" Then
        Call top3wa
    End If
    Application.ScreenUpdating = True
End Sub

Firstly, I love your name mumps Okay, I did everything you said and it worked perfectly. Not to be a pain but, this is a one-time action, I typed in the word Next again and hit enter again and it worked a second time. What would be really cool is if I could just click on the U5 cell now and every time I clicked on it, it would run the macro. So essentially be like assigning a macro to a button except not a button this time but a cell with text in it. I hope that's easy to understand, it was painful to write

 

[mumps]

I would suggest a double click on U5 instead of a simple click because if the macro runs on a click, this would prevent you from entering the cell without running the macro. See if this works for you using a double click. Of course, U5 must contain "Next" when you double click it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "U5" Then Exit Sub
    Application.ScreenUpdating = False
    If Target = "Next" Then
        Call top3wa
    End If
    Application.ScreenUpdating = True
End Sub

 

[TedX]

I would suggest a double click on U5 instead of a simple click because if the macro runs on a click, this would prevent you from entering the cell without running the macro. See if this works for you using a double click. Of course, U5 must contain "Next" when you double click it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "U5" Then Exit Sub
    Application.ScreenUpdating = False
    If Target = "Next" Then
        Call top3wa
    End If
    Application.ScreenUpdating = True
End Sub

Absolute genius, fair dinkum legend, that is exactly what I have been after for ages, wahoooooooooooooo can't thank you enough. I marked your post as the solution and am a very happy chappy even at 3.52 AM. Thanks mumps

 

[mumps]

You are very welcome.