CODEWITHSUNDEEP
excelvba
Dark
Dark

Reputation: 13

HyperLink click runs function twice and give patch error

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.

Upvotes: 1

Views: 536

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57703

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

Upvotes: 2

Related Questions