Comments Limited?

Danny Ray

Board Regular
Joined
May 28, 2005
Messages
79
Hello,
Can someone tell me if the number of characters in cell comments is limited?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Board Regular
Joined
May 28, 2005
Messages
79
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:

Code:
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:

Code:
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

Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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.

Danny Ray said:
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:

Code:
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

Board Regular
Joined
May 28, 2005
Messages
79

ADVERTISEMENT

Thanks, Tusharm.

The problem is when the following runs:

Code:
.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

Board Regular
Joined
May 28, 2005
Messages
79
Hello again,

I have resolved this situation.(I hope.)
I changed
Code:
.Offset(1, 1) = cell.Offset(0, 1)
To
Code:
.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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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
Code:
    .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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,501
Members
412,670
Latest member
Khin Zaw Htwe
Top