"Reference isn't valid" error when using hyperlink to run function

[jwbrouse01]

Greetings!

I am trying to use hyperlinks to run a macro which opens a userform. I previously found and adapted the below code to create the hyperlinks

Sub CreateHyperlinks()

With ActiveSheet
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

Range(Cells(2, 6), Cells(LastRow, 6)).Formula = "=HYPERLINK(""#MyUDF()"", ""Sign Up..."")"
  
End Sub


Function MyUDF()
      Set MyUDF = Selection 'This is required for the link to work properly
      MsgBox "The clicked cell address is " & Selection.row
End Function

When I try to click on the hyperlinks, I get an error message "Reference isn't valid" and the rest of the function does not run. I do apologize if this has already been solved elsewhere, but all my searches have brought up that it is critical to add the "Set MyUDF = Selection", otherwise you will get the aforementioned error.

Can someone please assist me? Thanks!

 

[RoryA]

The UDF call should not be in quotes or it is treated as a literal piece of text. It should be:

"=HYPERLINK(MyUDF(), ""Sign Up..."")"

 

[jwbrouse01]

Thank you for the reply.

If I make the recommended change, my hyperlinks display #NAME? and do not function.

I had also previously read that it is important to have the "#" in front of MyUDF(), but if I do that without the quotes, my CreateHyperlinks() sub errors out for a Run-time error '1004': Application-defined or object-defined error

 

[RoryA]

It works here. A Name error usually means you either have the code in the wrong place (it must be in a normal module and in the same workbook or an add in) or macros are disabled

 

[jwbrouse01]

Thank you. That was my problem. I had the function defined in a worksheet.

For my next problem...now, the function runs randomly. Click on the worksheet and leave it sit, nothing happens....but if I start moving my mouse around WITHOUT clicking on anything, it will randomly pop up the Msgbox defined in the MyUDF function.

Perhaps there is an better way to do all of this?

 

[jwbrouse01]

Update: it isn't random, it's whenever my mouse passes over one of the cells containing the hyperlink. I only want it to function when I click on the link.

 

[RoryA]

That's not how this method works. The whole point of it is that it works by mousing over the cell. If that's not what you want, you shoiuld use a different approach, such as actual hyperlinks and a worksheet_followhyperlink event, or just a button.