Hover over cell - display value from other cell

[virtuosok]

Hi,
I understand it's only possible to do so via VBA?
I have a range B3:X17 on Tab1, and I wonder if by hovering over, let's say, cell C10 in Tab1, I can see an entry from cell C10 in Tab2 from within the same workbook - so that it pops up as a ScreenTip. Same for all other cells. Thanks in advance for any ideas!

 

[SQUIDD]

Would it not be a way forward to write the tips in as comments?

But i guess you could make the code to insert text from tab 2 as a comment in tab 1?

dave

 

[SQUIDD]

This code will add the comments from b3 to x17. However, this may not suit your needs.

I dont think there is a hover over feature we can use in a sheet.

Sub add_comments()
For a = 3 To 17
    For b = 2 To 24
        With Sheets("Tab1").Cells(a, b)
            .AddComment
            .Comment.Text Text:=Sheets("Tab2").Cells(a, b).Text
        End With
    Next b
Next a
End Sub

 

[SQUIDD]

also FYI

if you are open to the user right clicking the cell, this will give a message box

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("B3:X17")) Is Nothing Then Exit Sub
    MsgBox Sheets("Tab2").Range(ActiveCell.Address)
    Cancel = True
End Sub

OR

this will add a comment, then remove it after the next right click event

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Range("B3:X17").ClearComments
If Intersect(Target, Range("B3:X17")) Is Nothing Then Exit Sub
    With ActiveCell
        .AddComment
        .Comment.Text Text:=Sheets("Tab2").Range(ActiveCell.Address).Text
    End With
Cancel = True
End Sub

just giving options

dave

 

[virtuosok]

GREAT! I like it the first solution!
Is there a way to reduce the default size of the comment field though, I have 2 to 6 characters to display only; a smaller comment box would look nicer

 

[SQUIDD]

hi

add this at the bottom of the code

For Each mycom In Application.ActiveSheet.Comments
    mycom.Shape.TextFrame.AutoSize = True
Next

 

[SQUIDD]

like this for ease

Sub add_comments()

For a = 3 To 17
    For b = 2 To 24
        With Sheets("Tab1").Cells(a, b)
            .AddComment
            .Comment.Text Text:=Sheets("Tab2").Cells(a, b).Text
        End With
    Next b
Next a

For Each mycom In Application.ActiveSheet.Comments
    mycom.Shape.TextFrame.AutoSize = True
Next

End Sub

 

[virtuosok]

Thanks! It doesn't auto-resize though, not sure why... saved of course as xlsm, reopened the file a few times...

 

[SQUIDD]

my mistake

sorry

try below, i used activesheet by mistake

Sub add_comments()

For a = 3 To 17
    For b = 2 To 24
        With Sheets("Tab1").Cells(a, b)
            .AddComment
            .Comment.Text Text:=Sheets("Tab2").Cells(a, b).Text
        End With
    Next b
Next a


For Each mycom In Application.Sheets("Tab1").Comments
    mycom.Shape.TextFrame.AutoSize = True
Next

End Sub

 

[virtuosok]

Still auto-resize is not working for me... but I can live with that
Thanks!