-1

I have a dropdown in i7. In j7 I have a formula, that adjusts the hyperlink -- based on value in i7. HLinks are to different cells in the same worksheet. Trying to get XL to automatically jump to j7 upon value change in i7, and to follow/execute the corresponding HLink, meaning for the j7 to act as if it was clicked on (but without the use of sendkeys-left mouse click). So far either line of below code, executed one at a time - on j7, gives "Run-time error '9': Subscript out of range"

Sub HLink_follow()

ActiveCell.Hyperlinks(1).Follow
ActiveWorkbook.FollowHyperlink ActiveCell.Hyperlinks(1).Address

End Sub

Am aware that this all can be done via VBA, without even having j7, but want to keep it the way that it is. If you are not clear on something, ask a question.

2Took
  • 35
  • 7
  • I guess your problem is that you read the Hyperlink from `Activecell`. When I7 is changed, that is the active cell, not J7. – FunThomas Jun 09 '22 at 13:18
  • No, code was run on j7. I have edited the original post now to make that abundantly clear, for such presumptions not to be made. – 2Took Jun 09 '22 at 14:54
  • 1
    How do yo trigger the code? Is it triggered by an event routine? Which? The error is because the active cell has no Hyperlink. Check with the debugger the address of the active cell. Also, consider to not work with ActiveCell but pass the cell you want to work with as parameter – FunThomas Jun 09 '22 at 16:12
  • For now, I'm triggering that code on j7 manually, just to work this specific part out. The other part should be more straight forward and planning on Worksheet_Change event for i7. – 2Took Jun 09 '22 at 16:32
  • Does the cell contain a hyperlink that is added via `Link`-menu or does it contain a formula using `Hyperlink`? – FunThomas Jun 10 '22 at 07:13
  • Hey, @FunThomas (are you in Europe somewhere?) - j7 contains the following formula: IF(COUNTIF(I7,"*x1*"),HYPERLINK("#A591","See pic"),IF(COUNTIF(I7,"*x2*"),HYPERLINK("#A665","See pic"), and so on... of course each x1, x2 etc has asterisks around them, which do not show up in this comment, once it's posted. Also, why would anyone vote this post down, it's odd. – 2Took Jun 10 '22 at 12:36

2 Answers2

0

When you use the Hyperlink-function in a formula, it doesn't add an entry to the hyperlink-collection of a cell. With other words, ActiveCell.Hyperlinks.Count is 0 and ActiveCell.Hyperlinks(1) therefore gives an Subscript out of range.

There is a question here on SO about that, have a look to this answer: https://stackoverflow.com/a/40343924/7599798. It suggests to parse the formula (split it by quote character) to get the URL and use FollowHyperlink with the extracted URL.

Now your case is more complicated as your cell doesn't contain only a simple =Hyperlink(..)-formula and therefore parsing is not an option. I see 2 possible solutions:

a) Instead of using a formula, add the Hyperlink via the Link-menu in Excel or the Worksheet.Hyperlink.Add method. Then add the logic to change the address in the Change-Trigger of your worksheet whenever some relevant data is changed.

b) Use a helper cell to calculate the destination of the hyperlink. The helper cell (let's say H7) would get a formula like IF(COUNTIF(I7,"x1"),"#A591",IF(COUNTIF(I7,"x2"),"#A665")). Your HLink_follow-routine would use the calculated address of that helper cell while the formula in J7 would have the simple formula =Hyperlink(H7, "See pic")

P.S. Yes, I am located in Europe - just look at my profile

FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Tried b): In j7 I have =HYPERLINK(K7, "See pic"). In K7 (helper cell) I have =IF(COUNTIF(I7,"*x1*"),"#A591",IF(COUNTIF(I7,"*x2*"),"#A665",IF(COUNTIF(I7,"*x3*"),"#A717",IF(COUNTIF(I7,"*x4*"),"#A770",IF(COUNTIF(I7,"*x5*"),"#A822",IF(COUNTIF(I7,"*x6*"),"#A890",IF(COUNTIF(I7,"*x7*"),"#A944",IF(COUNTIF(I7,"*x8*"),"#A1100",IF(COUNTIF(I7,"*x9*"),"#A1150"))))))))) j7 results in "See pic", when i7 drop down is used, and K7 displays corresponding cell, e.g. #A770. Clicking on j7 takes to that cell, but when running either line of my VBA above, on either j7 or k7, getting the same error. @FunThomas – 2Took Jun 10 '22 at 18:21
0
Private Sub Worksheet_Change(ByVal Target As Range)
'auto selects cell

Dim MyVariable As String
MyVariable = Range("k8").Value
Application.Goto Reference:=Range(MyVariable)

End Sub

K8 contains a substitute formula to strip "#" from K7. Works as expected.

2Took
  • 35
  • 7