Mouse hover/rollover dispaying data from different cell

[Smidge18]

Hi there and thanks for your taking the time to read this.

Is it possible to hover over a cell and display the value from a different cell.
I have a retirement fund sheet.
m7 is the fund value at the beginning of the year
m21 is the current value
m22 is percentage up or down for the year
m23 is value up or down for the year
the formula in m23 is: =M21-M7
as simple as it gets
V5 is how much I've spent from the account (I'm retired)
What I want to do, if possible, is to be able to hover over m23 and have it
display the value from another cell that has the formula: =SUM((M21-M7)+V5
so that it shows what the fund is actually doing, not counting my spending.
I want to do the same with m22, showing the fund's percentage up or down if I didn't
spend anything.
I don't have room to add any colums or rows.

Looking forward to hearing if it's possible. Thanks.

 

[James006]

Hi,

You can a look at an excellent solution created by Jordan Goldmeier ...

https://optionexplicitvba.com/2012/06/21/interactive-periodic-table-of-elements-in-excel/

Hope this will help

 

[Jaafar Tribak]

Perhaps an easier workaround than the hyperlink rollover effect suggested by James006 is the following code :

The code applies to Sheet1 and the cell where you want the comment to be displayed is Cell M23 ... Change as required.

In the ThisWorkbook Module:

Option Explicit

Private Type POINTAPI
    x As Long
    y As Long
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private WithEvents oCmbrsEvents As CommandBars

Const TARGET_SHEET = "Sheet1" [B][COLOR=#008000]'<=== Change Target Sheet to suit.[/COLOR][/B]
Const COMMENT_CELL = "M23"    [B][COLOR=#008000]'<=== Change comment Cell to suit.[/COLOR][/B]


Private Sub Workbook_Activate()
     Set oCmbrsEvents = Application.CommandBars
     Call oCmbrsEvents_OnUpdate
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
     Set oCmbrsEvents = Application.CommandBars
     Call oCmbrsEvents_OnUpdate
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Set oCmbrsEvents = Nothing
End Sub


Private Sub oCmbrsEvents_OnUpdate()

    Dim tCurPos As POINTAPI, oObject As Object
    
    On Error Resume Next
 
    If ActiveWorkbook Is Me Then
        If ActiveSheet.CodeName = TARGET_SHEET Then
            GetCursorPos tCurPos
            Set oObject = ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.y)
            If TypeName(oObject) = "Range" Then
                If oObject.Address(0, 0) = COMMENT_CELL Then
                    Set oObject = oObject.Comment
                    If oObject Is Nothing Then
                        With Range(COMMENT_CELL)
                            .AddComment "Cell M23 = " & CStr(Evaluate("SUM((M21-M7),V5)"))
                            .Comment.Shape.TextFrame.AutoSize = True
                            .Comment.Shape.TextFrame.Characters.Font.Color = vbRed
                            .Comment.Visible = True
                        End With
                    End If
                Else
                    Range(COMMENT_CELL).Comment.Delete
                End If
            Else
                Range(COMMENT_CELL).Comment.Delete
            End If
        End If
    End If
    
    With Application.CommandBars.FindControl(ID:=2020): .Enabled = Not .Enabled: End With
    
End Sub

 

[James006]

@ Jaafar

As usual a brilliant Solution ...

Love your approach ...

 

[Jaafar Tribak]

@ Jaafar

As usual a brilliant Solution ...

Love your approach ...

Thanks James for the feedback and glad you liked my workaround.

 

[Smidge18]

Thank you Jaafar for trying to help me with this. I've never used a code before so I've been googling all afternoon trying figure out how to follow your 'code' thingy. Here's what I've done: I opened my workbook then saved it as macro-enabled. Then I open that workbook and hit alt f11 and a new screen opens. On the left at the bottom of the top section, Microsoft Excel Objects is This Workbook. I double click on that and on the right side Option Explicit shows up. I paste your code from your response to me and delete one of the Option Explicit texts. I scroll down to where you've highlighted in green and change "Sheet1" to "Sheet3(2018)" Then I delete your green instructions. Then I hit alt q and go back to my worksheet. Then I go to view on the ribbon and click on macros....nothing is there. No macros listed. Sorry for the noobness but I've tried different ways to do this but am not getting any results. If you have the time, could you please let me know what I'm doing wrong. Thanks Forgot to mention that I'm using Excel 2013

 

[Jaafar Tribak]

Thank you Jaafar for trying to help me with this. I've never used a code before so I've been googling all afternoon trying figure out how to follow your 'code' thingy. Here's what I've done: I opened my workbook then saved it as macro-enabled. Then I open that workbook and hit alt f11 and a new screen opens. On the left at the bottom of the top section, Microsoft Excel Objects is This Workbook. I double click on that and on the right side Option Explicit shows up. I paste your code from your response to me and delete one of the Option Explicit texts. I scroll down to where you've highlighted in green and change "Sheet1" to "Sheet3(2018)" Then I delete your green instructions. Then I hit alt q and go back to my worksheet. Then I go to view on the ribbon and click on macros....nothing is there. No macros listed. Sorry for the noobness but I've tried different ways to do this but am not getting any results. If you have the time, could you please let me know what I'm doing wrong. Thanks Forgot to mention that I'm using Excel 2013

You won't see any macros listed because they are actually event macros so you won't have to run any code or macros.

The only thing that you haven't done correctly is how to edit the sheet name in the code .

Const TARGET_SHEET = "Sheet1"[COLOR=#008000][B] '<=== Change Target Sheet to suit.[/B][/COLOR]

You will need to change it with the sheet codename .

To find the actual sheet codename , do the following :

Just like before, go to the VBEeditor by hitting ALT+F11 and instead of double-cliccikng on the ThisWorkbook module , just select the corresponding sheet module .

Now, with the sheet module selected (ie: highlighted in blue) , on the bottom left of the of the VBEditor screen, there is another window ( Properties Window)

You will see the actual code name of the sheet at the very top of this Properties window.

That is the name you will need to copy to the above code constant... it should most likely read Sheet1 or Sheet2 or ... SheetX but not necessarly.

Once you are done putting the correct sheet codename just go back to excel (Alt+F11) again and select any cell in any worksheet and the code should kick off.

I hope I haven't confused you.

 

[Smidge18]

It took me a while to get it right, but that works like a charm. Truly amazing!!! Thank you very much. Now the last question: Can I modify your code slightly so that M22 would read: =SUM((M21+V5)/M7)-1 to get a percentage as basically the same question except percentage instead of dollar amount?

 

[Jaafar Tribak]

It took me a while to get it right, but that works like a charm. Truly amazing!!! Thank you very much. Now the last question: Can I modify your code slightly so that M22 would read: =SUM((M21+V5)/M7)-1 to get a percentage as basically the same question except percentage instead of dollar amount?

See if this works for you :

Code in the ThisWorkbook Module like before:

Option Explicit

Private Type POINTAPI
    x As Long
    y As Long
End Type

#If VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
#Else
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetActiveWindow Lib "user32" () As Long
#End If

Private WithEvents oCmbrsEvents As CommandBars

Private Const TARGET_SHEET = "Sheet1"   [B][COLOR=#008000]'<=== Change Target Sheet to suit.[/COLOR][/B]
Private Const COMMENT_CELL_1 = "M23"    [COLOR=#008000][B]'<=== Change first comment Cell to suit.[/B][/COLOR]
Private Const COMMENT_CELL_2 = "M22"    [B][COLOR=#008000]'<=== Change second comment Cell to suit.[/COLOR][/B]



Private Sub Workbook_Activate()
     Set oCmbrsEvents = Application.CommandBars
     Call oCmbrsEvents_OnUpdate
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
     Set oCmbrsEvents = Application.CommandBars
     Call oCmbrsEvents_OnUpdate
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Set oCmbrsEvents = Nothing
End Sub

Private Sub oCmbrsEvents_OnUpdate()

    Static oPrevRange As Range
    Dim oTargetRange As Range
    Dim oComment As Comment
    Dim sMsg As String
    Dim tCurPos As POINTAPI
    
    
    On Error Resume Next
    If GetActiveWindow = Application.Hwnd Then
        If ActiveWorkbook Is Me Then
            If ActiveSheet.CodeName = TARGET_SHEET Then
                GetCursorPos tCurPos
                Set oTargetRange = ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.y)
                If TypeName(oTargetRange) = "Range" Then
                    Select Case oTargetRange.Address(0, 0)
                        Case COMMENT_CELL_1
                            Set oTargetRange = Range(COMMENT_CELL_1)
                            sMsg = "$ " & CStr(Evaluate("SUM((M21-M7),V5)"))
                        Case COMMENT_CELL_2
                            Set oTargetRange = Range(COMMENT_CELL_2)
                            sMsg = Format(CStr(Evaluate("SUM((M21+V5)/M7)-1")), "Percent")
                        Case Else
                            Set oTargetRange = Nothing
                    End Select
                    If Not oTargetRange Is Nothing Then
                        Set oComment = oTargetRange.Comment
                        If oComment Is Nothing Then
                            With oTargetRange
                                oPrevRange.Comment.Delete
                                .AddComment "Cell " & oTargetRange.Address(0, 0) & "= " & sMsg
                                .Comment.Shape.TextFrame.AutoSize = True
                                .Comment.Shape.TextFrame.Characters.Font.Color = vbRed
                                .Comment.Visible = True
                            End With
                        End If
                    Else
                        Range(COMMENT_CELL_1).Comment.Delete
                        Range(COMMENT_CELL_2).Comment.Delete
                    End If
                Else
                    Range(COMMENT_CELL_1).Comment.Delete
                    Range(COMMENT_CELL_2).Comment.Delete
                End If
            End If
        End If
    End If
    Set oPrevRange = ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.y)
    With Application.CommandBars.FindControl(ID:=2020): .Enabled = Not .Enabled: End With
    
End Sub

 

[Smidge18]

Jaafar, thank you so much. It worked perfectly. Now I can click on a cell then hover over either of the target cells and see exactly what I was hoping to see. Thanks for going out of your way to help. I know that I'll never really get past the basic usage and understanding of Excel, but with people like you and others on this board and across the internet, I can still use some of the more complex attributes of Excel.

Thanks James for trying, too.