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?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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,029
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,029
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,504
Messages
5,832,106
Members
430,111
Latest member
Francis Xavier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top