Automate Comments Boxes

[LadyDi]

Is there a way to populate the comments box on one tab to a cell on another tab? I have a tab that contains information on various service calls and I have another tab with the call texts that the technicians enter describing what they had to do on another tab. I would like to set it up, so that the comments box for each service call on one tab will refelect that call's text on the second tab. Is that possible?

If found this code else where on this forum, but am not sure how to modify it so that it will show the correct call text with each call (there is a call number on both tabs that would link the two sets of data):

Sub CommentAddRange1()

Dim i As Long
Dim msg As String
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
msg = msg & Range("A" & i) & Chr(10)
Next i
msg = Trim(msg)

With Range("N2")
.ClearComments
.AddComment
.Comment.Text msg
End With

End Sub

 

[Rx_]

Hope I got the question right.
On your 1st sheet add some text on B3, B4, B5
On the 2nd sheet - the text from first sheet should appear as comments on the same cells.

Sub CopyTextToComment()
      ' Copies cell B3 value on sheet1 to same cell on sheet2 comment'
      ' Rx   Take two of these, apply to code and update me after the weekend.
      Dim X As Integer
      Dim Msg As String
10        For X = 3 To 5
          ' get text from sheet1
20        Sheets(1).Select
30        Range("B" & X).Select
40        Msg = Msg & Range("B" & X).Value & Chr(10)
          ' add text above to the comment on sheet2
50        Sheets(2).Select
60        Range("B" & X).AddComment
70        Range("B" & X).Comment.Visible = False
80        Range("B" & X).Comment.Text Text:="Rx: " & Chr(10) & Msg
90        Msg = "" ' clear out message or it will just keep getting longer
100       Next X
End Sub

Could always check to determine if it already has a comment
with a variation of If rng.Comment Is Nothing Then rng.AddComment

 

[LadyDi]

That works great. Thank you very much. I do have one more question. Is there a way to resize the comments box so that it will show the whole comment?

 

[Rx_]

So glad it was what you wanted! What are the odds? LOL

I added a comment that exceeds the standard comment box
Check the scaleWidth and scaleHeight
For a character size 10 - it should be somewhat proportinal to the character count [i.e. Len(msg)]
Remember, with proportinal fonts, it will be 'generally proportinal' to the lenth.
So, grab the lenght of your Msg, experiment and come with with a Width x Height that holds your comments.
Along the lines of
Select Case cint(Len(Msg)/100) ' 100 char per line
case 1
Selection.ShapeRange.ScaleWidth 3.00
Selection.ShapeRange.ScaleHeight 1.00 ' single line
case 2
Selection.ShapeRange.ScaleWidth 3.00
Selection.ShapeRange.ScaleHeight 2.00 ' double line
....

These are total guesses for size - please let us know what you find
Basically, for each 100 characters, add a height to give it a 2nd line
You could make it 30 characters or what ever you want.
Now, you have a custome size depending on the actual text.
It won't be perfectlly exact unless you use a typewriter font, but it will sure look customized.

Range("B3").Select
    Selection.ShapeRange.SetShapesDefaultProperties
    Range("D9").Comment.Text Text:= _
        "Rx: this is a really big comment for Lady Di because she is a really thankful person I am staying a few minutes after work on a Friday no less, to help her." & Chr(10) & ""
    Selection.ShapeRange.ScaleWidth 3.21, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 0.92, msoFalse, msoScaleFromTopLeft

 

[LadyDi]

Thank you very much for the code. I really appreciate you staying late just to help me.

I am having a little trouble with the code to adjust the size. Every time I try to run it, I get a message stating that "Object does not support this property or method". Is there by chance a reference that I need to add to get this to work?

 

[Rx_]

If you are running this from Access - yes. Plus you would have the obligatory object reference. Running from Excel, no.
If you can attach the code, I can check it tomorrow.
My bet is that you are missing a period or symbol where you concatenate the variables. It is always hard for me to see my own mistakes. I will be happy to look at it first thing tomorrow morning... with my bifocals LOL

 

[LadyDi]

Here is the code I was trying to use. I am using it in Excel.

110   For X = 2 To 125
120   Range("A" & X).Select
130       Selection.ShapeRange.SetShapesDefaultProperties
140       Select Case CInt(Len(Msg) / 100) ' 100 char per line
            Case 1
150         Selection.ShapeRange.ScaleWidth 3.21, msoFalse, msoScaleFromTopLeft
160         Selection.ShapeRange.ScaleHeight 1#, msoFalse, msoScaleFromTopLeft    ' single line
170         Case 2
180         Selection.ShapeRange.ScaleWidth 3.21, msoFalse, msoScaleFromTopLeft
190         Selection.ShapeRange.ScaleHeight 2#, msoFalse, msoScaleFromTopLeft    ' double line
200         End Select
210   Next X

Thank you again for all your help.

 

[Rx_]

found a bug in Excel 2010. The size does not set the top left properly.
This code segment is not the perfect solution as it does not adjust the height.
It will get you going today.
Will continue to look for a better soltuion that adjust both width and height dynamically

Sub AutoSizeComments()
Dim X As Integer
' demo for cells B2 through B10
' The AutoSize does not adjust the height
110   For X = 2 To 10
    With Range("B" & X)
    '.AddComment
        .Comment.Shape.TextFrame.AutoSize = True
    End With
Next X
End Sub

 

[LadyDi]

That works. Thank you very much.

 

[Rx_]

http://www.contextures.com/xlcomments03.html#Resize
The link above is worth looking at, will let you try it out. The Fonts can have colors, photos can be entered and there are other options for generic Comment By updates. There are some options to put the comments in the exact same spot (e.g. center of the window).

This code works on Excel 2010. Read that there might be a bug in Excel 2010 that requires the Left and Top. Added Top, Left just to cover the bases.
This code example only has 3 cases to get you started. Depending on the font used, the Width / Height are strickly for demo.
The Comment characters are counted then divided by 100 to get a relative size for the box. This returns case 1, 2 or else.
Each case provides a different size comment size.
In this example, the Width is the same - the height changes based on case 1, 2, else.
Note, this example starts with B2 - adjust as needed.
Would advise adding code to skip for "no comment"

Couldn't find anything like this. The link above would allow for adding hundreds of variations to customize your client's need.

Uncomment out line 11 if you want to visually see the cell being selected. But, selecting the cell is not necessary. For large ranges, uncommenting line 11 will take more time to run. Uncommenting it for testing is helpful.

Sub AdjustCommentSize()
      Dim CellComment As String
      Dim HoldMsg As String
      Dim CommentRelativeSize As Integer
       ' a bug in 2010 - must add top and left to fix
      ' Range("A1").Comment.Shape.TextFrame.AutoSize = True ' if you just want generic
10    For X = 2 To 10
11      ' Range("B" & X).Select ' It is not necessary to Select, but you can if you want to
20        CellComment = CellComment & Range("B" & X).Comment.Text & Chr(10)
          'Selection.ShapeRange.SetShapesDefaultProperties
30      CommentRelativeSize = CInt(Len(CellComment) / 100)
40        Select Case CommentRelativeSize ' 100 char per line
            Case 1
50              With Range("B" & X)
60                  With .Comment.Shape
70                      .Left = .Left - 363
80                      .Top = .Top - 300
90                      .Width = 300.5
100                     .Height = 50.5
110                 End With
120             End With
130       Case 2
140             With Range("B" & X)
150                 With .Comment.Shape
160                     .Left = .Left - 363
170                     .Top = .Top - 300
180                     .Width = 300.5
190                     .Height = 100.75
200                 End With
210             End With
220       Case Else
230             With Range("B" & X)
240                 With .Comment.Shape
250                     .Left = .Left - 363
260                     .Top = .Top - 300
270                     .Width = 300.5
280                     .Height = 300.95
290                 End With
300             End With
310         End Select
320   CellComment = ""
330   CommentRelativeSize = 0
340   Next X
End Sub

 

[LadyDi]

That works even better.

I looked at the link you provided too. That was interesting.

Thank you so much for your help.