Dynamically change comments, after change to another cell

[dslhs]

Hello,

I'm looking for a macro that will dynamically change the comments attached to a range of cells (entering the results from the formula in the cell), triggered by the change in value of another cell.

Specifically...

I'm looking for a macro that will be triggered by any changes to the contents of A2

Once triggered it will update all the comments attached to cells F7:F100, M7:M100, Z7:Z100, AA7:AA100, AB7:AB100, AC7:AC100 so that they include the results from the formula's in their respective cells.

(So once I change A2, the comments in F7 will reflect the results from the formula in F7, and the comments in F8 will reflect the results from the formula in F8, etc...).

I tried this, but I'm really bad at VBA so it didn't work:

Sub ValueToComment()
    Dim rCell As Range
    If Union(Target, Range("A2")).Address = Target.Address Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        sResult = Target.Value
        Target.ClearContents
    For Each rCell In Selection F7:F100, M7:M100, Z7:Z100, AA7:AA100, AB7:AB100, AC7:AC100
        With rCell
            If .HasFormula Then
                On Error Resume Next
                .Comment.Delete
                On Error GoTo 0
                .AddComment
                .Comment.Text Text:=CStr(rCell.Value)
            End If
        End With
    Next
    Set rCell = Nothing
End Sub

Any ideas?

Many thanks,

 

[Flaiban]

Maybe it will help you

Sub dslhsToComment()
Dim sResult As String

If Range("A2").Value <> "" Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    sResult = "DSLHS : " & Range("A2").Value

   For Each cell In Range("F7:F100,M7:M100,Z7:Z100,AA7:AA100,AB7:AB100,AC7:AC100").Cells
        Dim V As Range
        Set V = cell.Offset(0, 0)
        With cell
        .ClearComments
        If Not IsEmpty(V) Then
        .AddComment sResult
        End If
        End With
   Next
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End If

End Sub

 

[dslhs]

Hi,

Thanks for responding. That didn't quite work. That put "DSLHS : " and what was in A2 as the comment for every cell, once I ran the macro.

I would like the contents of each cell to be in their comments.

So, once I change A2, the contents (the return of a VLOOKUP) for F7 becomes the comment in F7. If I change A2 again, it refreshes the macro automatically and the VLOOKUP return in F7 goes into the comment attached to F7 again.

Thanks!

 

[HaHoBe]

Hi,

if you change the contents by hand you may use the Worksheet_Change-event behind the sheet in order to trigger the event (right-click on the worksheet tab and choose to see the code to get into the VBE at the proper place):

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rCell As Range
  If Not Intersect(Target, Range("A2")) Is Nothing Then
    For Each rCell In Range("F7:F100,M7:M100,Z7:Z100,AA7:AA100,AB7:AB100,AC7:AC100").SpecialCells(xlCellTypeFormulas, 23)
      With rCell
        On Error Resume Next
        .Comment.Delete
        On Error GoTo 0
        .AddComment
        .Comment.Text Text:=CStr(rCell.Value)
      End With
    Next rCell
  End If
End Sub

If you have run your macro before please make sure that Events are enabled by typing this line into the Immediate Window:

Application.EnableEvents = True

and hit enter. Any change to cell A2 should trigger the macro, only Formulacells in the given area should be affected.

Ciao,
Holger

 

[dslhs]

Brilliant. This works amazingly. Really really appreciate it.

There a few follow-ups.

1) I got this error:

Debugging doesn't seem to work. If I 'End' the macro works perfectly. But is that something to be concerned about?

2) Is there a way of increasing the size of the comments box? I know I can do it in individually to each comment by editing, but it then returns to original size with this macro. Is there a way of resizing within the macro? I found this command, but can't integrate:

Sub FitComments()
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
    xComment.Shape.TextFrame.AutoSize = True
Next
End Sub

3) Also - is there a way of setting it so that the comments are only created when the formula returns a value? If the value is "" can I delete the comment? This isn't a big issue, but would be an amazing addition.

Many many thanks

 

[HaHoBe]

Hi dslhs,

before doing anything else please go into the VBE and Check Tools/Refefrence for any broken references. If none are found please run Debug/Compile. If everything is fine from there you could change the codeline

  If Not Intersect(Target, Range("A2")) Is Nothing Then

which is working without errors for me not only here but in other workboos as well (finding out, if the triggered cell is A2) with just a check for the cell address like

  If Target.Address = Range("A2").Address Then

but the error could be triggered by anything else I suspect.

About the AutoSize of comments please add

        .AddComment
        .Comment.Text Text:=CStr(rCell.Value)
        .Comment.Shape.TextFrame.AutoSize = True    '///AutoSize comment box
      End With

Regarding your last question code may look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rCell As Range
  If Target.Address = Range("A2").Address Then
    For Each rCell In Range("F7:F100,M7:M100,Z7:Z100,AA7:AA100,AB7:AB100,AC7:AC100").SpecialCells(xlCellTypeFormulas, 23)
      With rCell
        On Error Resume Next
        .Comment.Delete
        On Error GoTo 0
        If .Value <> "" Then
          .AddComment
          .Comment.Text Text:=CStr(rCell.Value)
          .Comment.Shape.TextFrame.AutoSize = True    '///AutoSize comment box
        End If
      End With
    Next rCell
  End If
End Sub

Ciao,
Holger

 

[dslhs]

Thank you. You're very good at this!

One final question (hopefully).

Is there a way of setting it so that, if the comment box has fewer than 50 characters, the comment box autofits to the text, but if it has 50 or more than it has a specific scale height and width?

 

[HaHoBe]

Hi dslhs,

I wonder the results from the formulas will follow your wish to show a certain width and height unless you have integrated some line feeding characters in them as well...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rCell As Range
  If Target.Address = Range("A2").Address Then
    Application.ScreenUpdating = False
    For Each rCell In Range("F7:F100,M7:M100,Z7:Z100,AA7:AA100,AB7:AB100,AC7:AC100").SpecialCells(xlCellTypeFormulas, 23)
      With rCell
        On Error Resume Next
        .Comment.Delete
        On Error GoTo 0
        If .Value <> "" Then
          .AddComment
          With .Comment
            .Text Text:=CStr(rCell.Value)
            If Len(.Text) < 50 Then
              .Shape.TextFrame.AutoSize = True
            Else
              With .Shape.TextFrame
                .Height = 90
                .Width = 125
                .VerticalAlignment = xlVAlignCenter
                .HorizontalAlignment = xlHAlignCenter
              End With
            End If
          End With
        End If
      End With
    Next rCell
  End If
  Application.ScreenUpdating = True

End Sub

Formula Bar:

Part of the sheet:

One final question (hopefully).

Really?

Ciao,
Holger

 

[dslhs]

Really?

Of course not!

I'm getting an error:

and when I Debug, it highlights the 'Height':

Any advice?

Thank you for your help

 

[HaHoBe]

Hi dslhs,

it seems I reached the limit of what I know about comments I´m afraid I can´t be of any more help for you.