Control Comments (or a hover over function) with Another Cell

[bstboy]

Hello

How do I control the comments of a particular cell (let's just say Sheet 2, cell A1) with the data that is in another cell (say Sheet 1, cell B3). So when the user hovers over or looks at the comments of Sheet 2 Cell A1, they see what is contained in Sheet 1 cell B3.

Thanks

 

[mikerickson]

There are a couple of ways that one could do this.
All of them involve VBA.

What is in the cell receiving the comment adjustments (Sheet2!A1)? A formula or a user entered value?

Similarly, what is in the sending cell, formula or user entry?

 

[bstboy]

There are a couple of ways that one could do this.
All of them involve VBA.

What is in the cell receiving the comment adjustments (Sheet2!A1)? A formula or a user entered value?

Similarly, what is in the sending cell, formula or user entry?

Both are hardcoded values (user entry)

 

[mikerickson]

You could put this in Sheet1's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("B3")) Is Nothing Then
        On Error GoTo makeComment
        ThisWorkbook.Sheets("Sheet2").Range("A1").Comment.Text Text:=CStr(Range("B3").Value)
        On Error GoTo 0
    End If
    Exit Sub
makeComment:
    Err.Clear
    On Error Resume Next
    With ThisWorkbook.Sheets("Sheet2").Range("A1")
        .AddComment
    End With
    On Error GoTo DoubleError
    Resume
DoubleError:
    
End Sub

 

[bstboy]

You could put this in Sheet1's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("B3")) Is Nothing Then
        On Error GoTo makeComment
        ThisWorkbook.Sheets("Sheet2").Range("A1").Comment.Text Text:=CStr(Range("B3").Value)
        On Error GoTo 0
    End If
    Exit Sub
makeComment:
    Err.Clear
    On Error Resume Next
    With ThisWorkbook.Sheets("Sheet2").Range("A1")
        .AddComment
    End With
    On Error GoTo DoubleError
    Resume
DoubleError:
    
End Sub

Thanks so much. So if I wanted to do this with multiple cells, would I add some kind of AND function to this? Sorry but I have very little experience with VBA.

 

[mikerickson]

Here's one approach

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cellsShowingComments As Range
    Dim PrecedentsForComments As Range
    Dim oneCell As Range
    
    Set cellsShowingComments = ThisWorkbook.Sheets("Sheet2").Range("A1,A3,A4")
    Set PrecedentsForComments = Range("B3:D4")
    If Not Application.Intersect(Target, PrecedentsForComments) Is Nothing Then
        For Each oneCell In cellsShowingComments
            With oneCell
                On Error GoTo makeComment
                Select Case .Address
                    Case "$A$1": .Comment.Text Text:=CStr(Range("B3")) & "-" & CStr(Range("B4"))
                    Case "$A$3": .Comment.Text Text:=CStr(Range("C3")) & " of " & CStr(Range("C4"))
                    Case "$A$4": .Comment.Text Text:=CStr(Val(CStr(Range("D3"))) + Val(CStr(Range("D4"))))
                End Select
            End With
        Next oneCell
        On Error GoTo 0
    End If
    Exit Sub
    
makeComment:
    Err.Clear
    On Error Resume Next
    With oneCell
        .AddComment
    End With
    On Error GoTo DoubleError
    Resume
DoubleError:
    
End Sub