1

From title you can already tell what is my problem.

Somethink from my side:

I know there is for sure work around to run the macro once with creating boolean or check and hadling the error (with I still dont know how).

I want to know why the macro runs twice. My guess is that it takes mouse movement or somethink like that.

How to fix it?

Or use better alternative of capturing click on text (I want to evoid selection change/change and I am not big fan of using FollowHyperlink with linking on same cell).

Function I am using : =HYPERTEXTOVÝ.ODKAZ("#LinkClick()";"CLICK")

Eng version : =HYPERLINK("#LinkClick()";"CLICK")

Function LinkClick()

Range("A1").Value = Range("A1").Value + 1

End Function

It should be the same function. It is just different in my language :

https://support.office.com/cs-cz/article/hypertextov%C3%BD-odkaz-funkce-333c7ce6-c5ae-4164-9c47-7de9b76f577f

https://support.office.com/en-us/article/hyperlink-function-333c7ce6-c5ae-4164-9c47-7de9b76f577f?omkt=en-US&ui=en-US&rs=en-US&ad=US

PS: My first post and my english isn't best. Thanks for any answers.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dark
  • 13
  • 3
  • I didn't try it that way I found this post [excel hyperlink to nothing](https://stackoverflow.com/questions/33114093/excel-hyperlink-to-nothing) @Pᴇʜ – Dark May 12 '20 at 09:16

1 Answers1

2

You need to Set LinkClick = Selection so you return a cell with your function otherwise the link is invalid.

According to the documentation your formula =HYPERLINK("#LinkClick()";"CLICK") needs a link_location as first parameter HYPERLINK(link_location, [friendly_name]). But because you have a function call there "#LinkClick()" the function needs to return a valid link location, and that is what Set LinkClick = Selection does, it returns the actual selection as link location, so the hyperlink selects what is already selected (means it does nothing at all, but it doesn't complain about an invalid link location).

Option Explicit

Public Function LinkClick() As Range
    Set LinkClick = Selection 'make sure a valid link location is returned in the function

    ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value + 1
End Function
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 1
    I missed this part from his code. I used `Set LinkClick = Selection` by reflex and the function works as expected... I will delete my above comments. This has to be his problem! – FaneDuru May 12 '20 at 09:37
  • Oh yea thanks. I copy paste the other function and replace the set with my code (its not adding 1 to range A1 :D). So this was my stupidity. – Dark May 12 '20 at 10:12