Display cell from another tab in a mouse over tooltip

abezuidenhout

New Member
Joined
Sep 15, 2020
Messages
4
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Sep 15, 2020
Messages
4
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
So you're able to make custom changes? eg "content" can be formula, value, text
 

abezuidenhout

New Member
Joined
Sep 15, 2020
Messages
4
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

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

VBA Code:
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

New Member
Joined
Sep 15, 2020
Messages
4
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Forget about the code of my previous post, it doesn't take formulae into account :cry:.

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

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top