Insert value into Adjacent Cell, when Hyperlink is Clicked

[alexsheehan]

Hi folks

I currently have a column (column D) with a list of names and the names are clickable with the =HYPERLINK() formula;-

=IF($B7="","",IFERROR(HYPERLINK(A7,VLOOKUP(B7,Database!$A:$AH,12,0)&" "&VLOOKUP($B7,Database!$A:$AH,11,0)),""))

The cells all function and take me to the relevant section of the database when clicked - What I wish to know is, if it is possible to have the adjacent cell to where the HYPERLINK is located (in this case the adjacent cell is C7), populate with a value when the hyperlink is clicked and then for the hyperlink to be activated?

I have tried various ways, but I seem to keep falling short.

So far, the code I have is thus;-

If target.Offset(0,-1).Value = "þ" Then
        target.Offset(0,-1).Formula = "=if($D:$D="""","""",Hyperlink("""",""o""))"
        target.Offset(0,-1).Font.Name = "Wingdings"
        target.Offset(0,-1).Font.Underline = False
        target.Font.Size = 14
 
    Else
        target.Offset(0,-1).Value = "o"
        target.Offset(0,-1).Formula = "=if($D:$D="""","""",Hyperlink("""",""þ""))"
        target.Font.Name = "Wingdings"
        target.Font.Underline = False
        target.Font.Size = 14
    End If

Does this make sense?

If someone could help me or let me know if this is not possible I would be eternally grateful! It's probably something really obvious that overlooking knowing me!

Best regards

Alex

 

[p45cal]

Your quoted code looks as if it's in an event handler.. which one?

 

[Debraj]

Hi Alex,

Please check the attached...
There was lot of difference between ..

• http://chandoo.org/forum/threads/insert-value-into-adjacent-cell-when-hyperlink-is-clicked.13002/
• and =HYPERLINK("http://chandoo.org/forum/threads/insert-value-into-adjacent-cell-when-hyperlink-is-clicked.13002/")

wait, we have Jordan, for "Mouse Roll Over" effect, I wish he also have some technique for mouse click on Formula Hyperlink, I can increase +1 in case of roll over, but, only if clicked, still out of reach..

and for green one..

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    ActiveCell.Offset(, 1) = ActiveCell.Offset(, 1) * 1 + 1
End Sub

 

[alexsheehan]

Thanks very much for your help guys.

Debraj - This is just a typical example of me over-thinking things, your code

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    ActiveCell.Offset(, 1) = ActiveCell.Offset(, 1) * 1 + 1
End Sub

works like a dream when the hyperlink is taking me to an external (web-based) link (thank you very much!), however, my links are taking me to another tab/section of the current Workbook. Unfortunately, this code does not seem to function in this instance - we're definitely on the right track though!

To clarify, I click the link in my 'Search' tab, which successfully takes me to the relevant record in my 'Database' tab, but the number adjacent to the link in my 'Search' tab does not get incremented.

Any suggestions for getting this code to work with links referencing a tab within the same workbook?

Thanks very much for your help so far.

Alex