Comments Limited?

[Danny Ray]

Hello,
Can someone tell me if the number of characters in cell comments is limited?

 

[tusharm]

Why not create a comment with the largest number of characters you are likely to need? That will tell you if you are likely to have a problem with whatever system you are designing.

 

[Danny Ray]

Hello Tusharm,
The problem I am having is that I have written a code to copy comments to a comment sheet. It works well,however, somwtimes I get #Value instead of the comment. I thought that the comments box may be limited, but the comments stay in the comment box. I don't know where the problem is.

Any help you can give is very appreciated. I have posted my codes below.



Copy comments to sheet2:

Sub Save_Comments()
On Error GoTo errhandler
For Each oCell In Range("al1:al1000").SpecialCells(xlCellTypeComments)
    Select Case oCell.Comment.Text
    Case Is <> ""
   holder = oCell.Comment.Text
 oCell.Comment.Delete
 oCell.AddComment holder & vbCrLf & vbCrLf & vbCrLf & vbCrLf _
 & Space(75) & Environ("Username") & vbCrLf & Space(75) & Date

    Sheets("sheet2").Range("a65536").End(xlUp).Offset(1, 0) = oCell.Offset(0, -34)
    Sheets("Sheet2").Range("a65536").End(xlUp).Offset(0, 1) = oCell.Comment.Text
    End Select
Next
For Each oCell In Range("al1:al1000").SpecialCells(xlCellTypeComments)
oCell.ClearComments
Next
errhandler:
Exit Sub
End Sub

Retrieve Comments:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 39 Then Call View_Comments
If Target.Column = 38 Then
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
ActiveCell.Comment.Shape.Select
Else
Exit Sub
End If
End Sub

Sub View_Comments()

 For Each cell In Sheets("sheet2").Range("a2:a1000")
      If cell = ActiveCell.Offset(0, -35) And cell <> "" Then
        With Sheets("Comments Archive").Range("a65536").End(xlUp)
         .Offset(1, 0) = cell
         .Offset(1, 1) = cell.Offset(0, 1)
         End With
      End If
   Next
     Sheets("Comments Archive").Visible = True
     Sheets("Comments Archive").Select
End Sub

 

[tusharm]

First of all, thanks for formatting the code for readability. Too many cannot or will not show common courtesy.

You don't indicate where the problem arises but I am always suspicious of code with an error handler. All that it does is mask design/programming flaws. Get rid off it (at least temporarily) and it may help you figure out where the problem lies.

Hello Tusharm,
The problem I am having is that I have written a code to copy comments to a comment sheet. It works well,however, somwtimes I get #Value instead of the comment. I thought that the comments box may be limited, but the comments stay in the comment box. I don't know where the problem is.

Any help you can give is very appreciated. I have posted my codes below.



Copy comments to sheet2:

Sub Save_Comments()
On Error GoTo errhandler
For Each oCell In Range("al1:al1000").SpecialCells(xlCellTypeComments)
    Select Case oCell.Comment.Text
    Case Is <> ""
   holder = oCell.Comment.Text
 oCell.Comment.Delete
 oCell.AddComment holder & vbCrLf & vbCrLf & vbCrLf & vbCrLf _
 & Space(75) & Environ("Username") & vbCrLf & Space(75) & Date

    Sheets("sheet2").Range("a65536").End(xlUp).Offset(1, 0) = oCell.Offset(0, -34)
{snip}[/quote]

 

[Danny Ray]

Thanks, Tusharm.

The problem is when the following runs:

.Offset(1, 1) = cell.Offset(0, 1)

I tried it with different possibilities as you suggested. After 79 characters, #Value is returned. I can live with that. I just didn't know that there was a limit to the number of characters that can be transferred this way.
Do you know of a help page or site that states the limits of these types of things?

 

[Danny Ray]

Hello again,

I have resolved this situation.(I hope.)
I changed

.Offset(1, 1) = cell.Offset(0, 1)

To

.Offset(1, 1) = cell.Offset(0, 1).Value

I was wrong about the limitations. Now, it seems to accept whatever I enter.

Thank you, Tusharm, for your help and tolerance of my ignorance.
I very much appreciate it.

 

[tusharm]

You are welcome. Of course, other than point you in the right direction, I didn't do much!

Now, take the lesson and apply it across the board. Don't rely on default properties/methods. *Always* make what you mean explicit. For example, when it comes to the range object, never ever rely on Value being the default property. I would have written the code as

    .Offset(1, 1).Value = cell.Offset(0, 1).Value

Not only will it help you with VB(A) but it is a requirement with .Net. If you do away with the reliance of defaults now, it will be that much easier if and when you transition to .Net.