Thanks to peoplehere, I have a script that's working great for me but it
involves settings comments for cells. I want to to some more advanced things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text to the
box in the VBA script? I tried using vbCr and vbCrLn which work but it puts
a square symbol in the comments instead of just a lien return. I'm assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only big
enough ti display whatever text is actually in there? Each cell will have
different amounts of text and I don't want tons of blank space if there's
just one line of text and don't want it cut off if there's 5 lines...

3) Can I concatenate comments if comments already exist?

This code snippet will show you how to handle questions #1 and #3 (I'm not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me but it
involves settings comments for cells. I want to to some more advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text to the
box in the VBA script? I tried using vbCr and vbCrLn which work but it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only big
enough ti display whatever text is actually in there? Each cell will have
different amounts of text and I don't want tons of blank space if there's
just one line of text and don't want it cut off if there's 5 lines...

3) Can I concatenate comments if comments already exist?

For #2, to autosize
..Comment.Shape.TextFrame.AutoSize = True

I think better to change vbNewLine to vbLf

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote in
message ...
This code snippet will show you how to handle questions #1 and #3 (I'm not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me but it
involves settings comments for cells. I want to to some more advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text to
the
box in the VBA script? I tried using vbCr and vbCrLn which work but it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only big
enough ti display whatever text is actually in there? Each cell will
have
different amounts of text and I don't want tons of blank space if
there's
just one line of text and don't want it cut off if there's 5 lines...

3) Can I concatenate comments if comments already exist?

TextFrame.AutoSize ... I completely missed that one (I knew it something
like that had to exist, I just couldn't find it).

As for vbNewLine (which I like using over its more familiar form, vbCrLf,
because of its descriptive name) compared to vbLf... like many Windows
objects, it doesn't seem to matter which you use... if you use vbNewLine,
then both the carriage return and line feed are placed in the text string;
if you use vbLf, then just the line feed is placed in the text string; but
either way, the Comment box seems to know how to display them each
correctly.

Rick


"Peter T" <peter_t@discussions wrote in message
...
For #2, to autosize
.Comment.Shape.TextFrame.AutoSize = True

I think better to change vbNewLine to vbLf

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote in
message ...
This code snippet will show you how to handle questions #1 and #3 (I'm
not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me but
it
involves settings comments for cells. I want to to some more advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text to
the
box in the VBA script? I tried using vbCr and vbCrLn which work but it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only big
enough ti display whatever text is actually in there? Each cell will
have
different amounts of text and I don't want tons of blank space if
there's
just one line of text and don't want it cut off if there's 5 lines...

3) Can I concatenate comments if comments already exist?

As for vbNewLine compared to vbLf... like many Windows
objects, it doesn't seem to matter which you use...

For me (version?) it's the difference between seeing the little square or
not at the end of the line (in a Comment). IOW it's the vbCr/chr(13) that
produces that little square.

Similarly for cells -
ActiveCell.Value = "A" & vbNewLine & "B" ' little square
ActiveCell.Offset(, 1).Value = "A" & vbLf & "B" ' no square

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote in
message ...
TextFrame.AutoSize ... I completely missed that one (I knew it something
like that had to exist, I just couldn't find it).

As for vbNewLine (which I like using over its more familiar form, vbCrLf,
because of its descriptive name) compared to vbLf... like many Windows
objects, it doesn't seem to matter which you use... if you use vbNewLine,
then both the carriage return and line feed are placed in the text string;
if you use vbLf, then just the line feed is placed in the text string; but
either way, the Comment box seems to know how to display them each
correctly.

Rick


"Peter T" <peter_t@discussions wrote in message
...
For #2, to autosize
.Comment.Shape.TextFrame.AutoSize = True

I think better to change vbNewLine to vbLf

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote
in
message ...
This code snippet will show you how to handle questions #1 and #3 (I'm
not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me but
it
involves settings comments for cells. I want to to some more
advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text
to
the
box in the VBA script? I tried using vbCr and vbCrLn which work but
it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only
big
enough ti display whatever text is actually in there? Each cell will
have
different amounts of text and I don't want tons of blank space if
there's
just one line of text and don't want it cut off if there's 5 lines...

3) Can I concatenate comments if comments already exist?

"Peter T" <peter_t@discussions wrote in message
...
As for vbNewLine compared to vbLf... like many Windows
objects, it doesn't seem to matter which you use...

For me (version?) it's the difference between seeing the little square or
not at the end of the line (in a Comment). IOW it's the vbCr/chr(13) that
produces that little square.

Similarly for cells -
ActiveCell.Value = "A" & vbNewLine & "B" ' little square
ActiveCell.Offset(, 1).Value = "A" & vbLf & "B" ' no square

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote in
message ...
TextFrame.AutoSize ... I completely missed that one (I knew it something
like that had to exist, I just couldn't find it).

As for vbNewLine (which I like using over its more familiar form, vbCrLf,
because of its descriptive name) compared to vbLf... like many Windows
objects, it doesn't seem to matter which you use... if you use vbNewLine,
then both the carriage return and line feed are placed in the text
string;
if you use vbLf, then just the line feed is placed in the text string;
but
either way, the Comment box seems to know how to display them each
correctly.

Rick


"Peter T" <peter_t@discussions wrote in message
...
For #2, to autosize
.Comment.Shape.TextFrame.AutoSize = True

I think better to change vbNewLine to vbLf

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote
in
message ...
This code snippet will show you how to handle questions #1 and #3 (I'm
not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me
but
it
involves settings comments for cells. I want to to some more
advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text
to
the
box in the VBA script? I tried using vbCr and vbCrLn which work but
it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only
big
enough ti display whatever text is actually in there? Each cell
will
have
different amounts of text and I don't want tons of blank space if
there's
just one line of text and don't want it cut off if there's 5
lines...

3) Can I concatenate comments if comments already exist?

Hmm! I don't see the little square... I see a properly displayed 2-line
comment instead (using XL2003). I'll have to make a mental note to only use
the line feed character from now on for compatibility with other versions.
Thanks for pointing this out to me.

Rick


"Peter T" <peter_t@discussions wrote in message
...
As for vbNewLine compared to vbLf... like many Windows
objects, it doesn't seem to matter which you use...

For me (version?) it's the difference between seeing the little square or
not at the end of the line (in a Comment). IOW it's the vbCr/chr(13) that
produces that little square.

Similarly for cells -
ActiveCell.Value = "A" & vbNewLine & "B" ' little square
ActiveCell.Offset(, 1).Value = "A" & vbLf & "B" ' no square

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote in
message ...
TextFrame.AutoSize ... I completely missed that one (I knew it something
like that had to exist, I just couldn't find it).

As for vbNewLine (which I like using over its more familiar form, vbCrLf,
because of its descriptive name) compared to vbLf... like many Windows
objects, it doesn't seem to matter which you use... if you use vbNewLine,
then both the carriage return and line feed are placed in the text
string;
if you use vbLf, then just the line feed is placed in the text string;
but
either way, the Comment box seems to know how to display them each
correctly.

Rick


"Peter T" <peter_t@discussions wrote in message
...
For #2, to autosize
.Comment.Shape.TextFrame.AutoSize = True

I think better to change vbNewLine to vbLf

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote
in
message ...
This code snippet will show you how to handle questions #1 and #3 (I'm
not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me
but
it
involves settings comments for cells. I want to to some more
advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text
to
the
box in the VBA script? I tried using vbCr and vbCrLn which work but
it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only
big
enough ti display whatever text is actually in there? Each cell
will
have
different amounts of text and I don't want tons of blank space if
there's
just one line of text and don't want it cut off if there's 5
lines...

3) Can I concatenate comments if comments already exist?

I'm using XL2000, guess that's the difference. I thought it was same for all
versions(vbCr = square, vblF only = no square), but from your report it
seems not.

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote in
message ...
Hmm! I don't see the little square... I see a properly displayed 2-line
comment instead (using XL2003). I'll have to make a mental note to only
use
the line feed character from now on for compatibility with other versions.
Thanks for pointing this out to me.

Rick


"Peter T" <peter_t@discussions wrote in message
...
As for vbNewLine compared to vbLf... like many Windows
objects, it doesn't seem to matter which you use...

For me (version?) it's the difference between seeing the little square
or
not at the end of the line (in a Comment). IOW it's the vbCr/chr(13)
that
produces that little square.

Similarly for cells -
ActiveCell.Value = "A" & vbNewLine & "B" ' little square
ActiveCell.Offset(, 1).Value = "A" & vbLf & "B" ' no square

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote
in
message ...
TextFrame.AutoSize ... I completely missed that one (I knew it
something
like that had to exist, I just couldn't find it).

As for vbNewLine (which I like using over its more familiar form,
vbCrLf,
because of its descriptive name) compared to vbLf... like many Windows
objects, it doesn't seem to matter which you use... if you use
vbNewLine,
then both the carriage return and line feed are placed in the text
string;
if you use vbLf, then just the line feed is placed in the text string;
but
either way, the Comment box seems to know how to display them each
correctly.

Rick


"Peter T" <peter_t@discussions wrote in message
...
For #2, to autosize
.Comment.Shape.TextFrame.AutoSize = True

I think better to change vbNewLine to vbLf

Regards,
Peter T
<snip

Well that helps but I'm stuck somewhere.

When I put the: "OldComment = .Comment.Text" in before my .addComment
line, I get an error that says Object Variable not set.

If I put it after the .Addcomment line, then it works just fine.

What I need to do is say If a cell already has a comment, take that comment
and add in the new informatino to the comment. But if it doesn't have a
comment then just add the new information.

I tried to do this:

OldComment = .Comment.Text
If OldComment < Null Then OldComment = OldComment & vbNewLine
.AddComment OldComment & "New Information"

It bails out right at the Oldcomment=.comment.text line.

Do I have to initialize the cell somehow if it doesn't have a comment maybe?
Not sure what to do.

Thanks!


"Rick Rothstein (MVP - VB)" wrote:

This code snippet will show you how to handle questions #1 and #3 (I'm not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me but it
involves settings comments for cells. I want to to some more advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text to the
box in the VBA script? I tried using vbCr and vbCrLn which work but it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only big
enough ti display whatever text is actually in there? Each cell will have
different amounts of text and I don't want tons of blank space if there's
just one line of text and don't want it cut off if there's 5 lines...

3) Can I concatenate comments if comments already exist?

"Mr B" wrote in message
...
Well that helps but I'm stuck somewhere.

When I put the: "OldComment = .Comment.Text" in before my .addComment
line, I get an error that says Object Variable not set.

If I put it after the .Addcomment line, then it works just fine.

What I need to do is say If a cell already has a comment, take that
comment
and add in the new informatino to the comment. But if it doesn't have a
comment then just add the new information.

I tried to do this:

OldComment = .Comment.Text
If OldComment < Null Then OldComment = OldComment & vbNewLine
.AddComment OldComment & "New Information"

It bails out right at the Oldcomment=.comment.text line.

Do I have to initialize the cell somehow if it doesn't have a comment
maybe?
Not sure what to do.

Thanks!


"Rick Rothstein (MVP - VB)" wrote:

This code snippet will show you how to handle questions #1 and #3 (I'm
not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me but
it
involves settings comments for cells. I want to to some more advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text to
the
box in the VBA script? I tried using vbCr and vbCrLn which work but it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only big
enough ti display whatever text is actually in there? Each cell will
have
different amounts of text and I don't want tons of blank space if
there's
just one line of text and don't want it cut off if there's 5 lines...

3) Can I concatenate comments if comments already exist?

Sorry, I forgot to account for the fact that there might not be a comment in
the cell. Try it this way...

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("D5")
If Not .Comment Is Nothing Then
OldComment = .Comment.Text
.Comment.Delete
End If
If Len(OldComment) 0 Then OldComment = OldComment & vbLf
.AddComment OldComment & "I'm on my own line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Well that helps but I'm stuck somewhere.

When I put the: "OldComment = .Comment.Text" in before my .addComment
line, I get an error that says Object Variable not set.

If I put it after the .Addcomment line, then it works just fine.

What I need to do is say If a cell already has a comment, take that
comment
and add in the new informatino to the comment. But if it doesn't have a
comment then just add the new information.

I tried to do this:

OldComment = .Comment.Text
If OldComment < Null Then OldComment = OldComment & vbNewLine
.AddComment OldComment & "New Information"

It bails out right at the Oldcomment=.comment.text line.

Do I have to initialize the cell somehow if it doesn't have a comment
maybe?
Not sure what to do.

Thanks!


"Rick Rothstein (MVP - VB)" wrote:

This code snippet will show you how to handle questions #1 and #3 (I'm
not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me but
it
involves settings comments for cells. I want to to some more advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text to
the
box in the VBA script? I tried using vbCr and vbCrLn which work but it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only big
enough ti display whatever text is actually in there? Each cell will
have
different amounts of text and I don't want tons of blank space if
there's
just one line of text and don't want it cut off if there's 5 lines...

3) Can I concatenate comments if comments already exist?

Perfect!

Thanks a Ton.

And I'm on Excel 2003 as well so the vbLf gave me the no little box which
worked fine.

"Rick Rothstein (MVP - VB)" wrote:

Sorry, I forgot to account for the fact that there might not be a comment in
the cell. Try it this way...

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("D5")
If Not .Comment Is Nothing Then
OldComment = .Comment.Text
.Comment.Delete
End If
If Len(OldComment) 0 Then OldComment = OldComment & vbLf
.AddComment OldComment & "I'm on my own line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Well that helps but I'm stuck somewhere.

When I put the: "OldComment = .Comment.Text" in before my .addComment
line, I get an error that says Object Variable not set.

If I put it after the .Addcomment line, then it works just fine.

What I need to do is say If a cell already has a comment, take that
comment
and add in the new informatino to the comment. But if it doesn't have a
comment then just add the new information.

I tried to do this:

OldComment = .Comment.Text
If OldComment < Null Then OldComment = OldComment & vbNewLine
.AddComment OldComment & "New Information"

It bails out right at the Oldcomment=.comment.text line.

Do I have to initialize the cell somehow if it doesn't have a comment
maybe?
Not sure what to do.

Thanks!


"Rick Rothstein (MVP - VB)" wrote:

This code snippet will show you how to handle questions #1 and #3 (I'm
not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me but
it
involves settings comments for cells. I want to to some more advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text to
the
box in the VBA script? I tried using vbCr and vbCrLn which work but it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only big
enough ti display whatever text is actually in there? Each cell will
have
different amounts of text and I don't want tons of blank space if
there's
just one line of text and don't want it cut off if there's 5 lines...

3) Can I concatenate comments if comments already exist?

Just for curiosity, how did the vbNewLine appear for you in XL2003

Regards,
Peter T

"Mr B" wrote in message
...
Perfect!

Thanks a Ton.

And I'm on Excel 2003 as well so the vbLf gave me the no little box which
worked fine.

"Rick Rothstein (MVP - VB)" wrote:

Sorry, I forgot to account for the fact that there might not be a comment
in
the cell. Try it this way...

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("D5")
If Not .Comment Is Nothing Then
OldComment = .Comment.Text
.Comment.Delete
End If
If Len(OldComment) 0 Then OldComment = OldComment & vbLf
.AddComment OldComment & "I'm on my own line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Well that helps but I'm stuck somewhere.

When I put the: "OldComment = .Comment.Text" in before my .addComment
line, I get an error that says Object Variable not set.

If I put it after the .Addcomment line, then it works just fine.

What I need to do is say If a cell already has a comment, take that
comment
and add in the new informatino to the comment. But if it doesn't have
a
comment then just add the new information.

I tried to do this:

OldComment = .Comment.Text
If OldComment < Null Then OldComment = OldComment & vbNewLine
.AddComment OldComment & "New Information"

It bails out right at the Oldcomment=.comment.text line.

Do I have to initialize the cell somehow if it doesn't have a comment
maybe?
Not sure what to do.

Thanks!


"Rick Rothstein (MVP - VB)" wrote:

This code snippet will show you how to handle questions #1 and #3 (I'm
not
sure how to do #2)....

Sub Test()
Dim OldComment As String
With Worksheets("Sheet1").Range("C5")
OldComment = .Comment.Text
.Comment.Delete
.AddComment OldComment & vbNewLine & "I'm on the next line."
End With
End Sub

Rick


"Mr B" wrote in message
...
Thanks to peoplehere, I have a script that's working great for me
but
it
involves settings comments for cells. I want to to some more
advanced
things
with the Comments box but don't know how.

1) How can I add a Line Return in the comments box when adding text
to
the
box in the VBA script? I tried using vbCr and vbCrLn which work but
it
puts
a square symbol in the comments instead of just a lien return. I'm
assuming
there's some other coed I should use?

2) Is there a way to set the size of the comments box so it's only
big
enough ti display whatever text is actually in there? Each cell
will
have
different amounts of text and I don't want tons of blank space if
there's
just one line of text and don't want it cut off if there's 5
lines...

3) Can I concatenate comments if comments already exist?

When I use vbNewLine I get a vertical line at the end of the line before the
return.

At least on my work machine...


"Peter T" wrote:

Just for curiosity, how did the vbNewLine appear for you in XL2003

Regards,
Peter T

That's really odd. I just tried it again, to be sure, and I still do not get
any vertical lines in my comments when I use vbNewLine. Since we are both
using XL2003, I am at a loss to explain why our two systems react
differently to this. If it matters any, my copy of Excel shows I am using
SP3 (my version is listed as 11.8211.8202).

Rick


"Mr B" wrote in message
...
When I use vbNewLine I get a vertical line at the end of the line before
the
return.

At least on my work machine...


"Peter T" wrote:

Just for curiosity, how did the vbNewLine appear for you in XL2003

Regards,
Peter T