Display cell from another tab in a mouse over tooltip

[abezuidenhout]

Hi,

On sheet1 of my spreadsheet, when you hover your mouse over a cell in row C (say C12), it displays a link to a cell from tab 2 (sheet2!H5)

Is there a way I can use hyperlink tooltips to display the content of sheet2!H5 instead of the link to it?

Thank you!

 

[GWteB]

Hi @abezuidenhout, welcome to MrExcel.
There is a way to accomplish your requirement, VBA is needed though.
If you agree with that I can write some code. Are you familiar with VBA?

 

[abezuidenhout]

Hi @abezuidenhout, welcome to MrExcel.
There is a way to accomplish your requirement, VBA is needed though.
If you agree with that I can write some code. Are you familiar with VBA?

Thank you I'd appreciate any ideas! I'm senior level in vba, so fire away and thank you so much!

 

[GWteB]

So you're able to make custom changes? eg "content" can be formula, value, text

 

[abezuidenhout]

So you're able to make custom changes? eg "content" can be formula, value, text

I'm not quite sure what you mean by that. I've been working on MS Access for about 20 years, including SQL/Oracle backends, so I'm very familiar with VBA and what it can do. I'm new in Excel though, only been doing that for about 6 months now. The VBA is quite the same, but how to trigger it from cells is a little vague still

 

[GWteB]

The code below does what you want. Some explanation may be desired.
The code runs in the module of the worksheet referenced in the hyperlink, ie Sheet2. The code is triggered when a change event of Sheet2 occurs and checks if cell H5 on Sheet2 is involved. If that's the case a search on Sheet1 is performed for all hyperlinks that are linked to cell H5 on Sheet2. When found, the required information is assigned to the ScreenTip property of the found hyperlink.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const cCellToWatch As String = "$H$5"

    Dim oHl As Hyperlink, r As Range

    If Not Intersect(Me.Range(cCellToWatch), Target) Is Nothing Then
        For Each oHl In ThisWorkbook.Sheets("Sheet1").Hyperlinks
            If Range(oHl.SubAddress).Address = cCellToWatch Then
                Set r = Me.Range(cCellToWatch)
                On Error Resume Next
                oHl.ScreenTip = Me.Name & " - " & r.Address & vbNewLine & _
                                "Formula: " & r.Formula & vbNewLine & _
                                "Value: " & r.Value & vbNewLine & _
                                "Text: " & r.Text
                On Error GoTo 0
            End If
        Next oHl
    End If
End Sub

 

[GWteB]

Forget about the code of my previous post, it doesn't take formulae into account .

Paste the code below in the module of Sheet2, all should work now as expected.

Option Explicit

Private Const cCellToWatch As String = "$H$5"

Private Sub Worksheet_Calculate()
    UpdateHyperlinks
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Me.Range(cCellToWatch), Target) Is Nothing Then
        UpdateHyperlinks
    End If
End Sub

Private Sub UpdateHyperlinks()

    Dim oHl As Hyperlink, r As Range

    For Each oHl In ThisWorkbook.Sheets("Sheet1").Hyperlinks
        If Range(oHl.SubAddress).Address = cCellToWatch Then
            Set r = Me.Range(cCellToWatch)
            oHl.ScreenTip = Me.Name & " - " & r.Address & vbNewLine & _
                            "Formula: " & r.Formula & vbNewLine & _
                            "Value   : " & CStr(r.Value) & vbNewLine & _
                            "Text    : " & r.Text & vbNewLine & _
                            "Value2 : " & CStr(r.Value2)
        End If
    Next oHl
End Sub

 

[abezuidenhout]

Forget about the code of my previous post, it doesn't take formulae into account .

Paste the code below in the module of Sheet2, all should work now as expected.

Option Explicit

Private Const cCellToWatch As String = "$H$5"

Private Sub Worksheet_Calculate()
    UpdateHyperlinks
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Me.Range(cCellToWatch), Target) Is Nothing Then
        UpdateHyperlinks
    End If
End Sub

Private Sub UpdateHyperlinks()

    Dim oHl As Hyperlink, r As Range

    For Each oHl In ThisWorkbook.Sheets("Sheet1").Hyperlinks
        If Range(oHl.SubAddress).Address = cCellToWatch Then
            Set r = Me.Range(cCellToWatch)
            oHl.ScreenTip = Me.Name & " - " & r.Address & vbNewLine & _
                            "Formula: " & r.Formula & vbNewLine & _
                            "Value   : " & CStr(r.Value) & vbNewLine & _
                            "Text    : " & r.Text & vbNewLine & _
                            "Value2 : " & CStr(r.Value2)
        End If
    Next oHl
End Sub

Thank you so much. I haven't had time to look at anything since your last post, so I guess I didn't see that it didn't work This one works great thank you so much, I really appreciate your help!

Andrea

 

[GWteB]

You are welcome and thanks for letting me know.