|
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
"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? |
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
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 |
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
"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? |
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
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? |
Working with Cell Comments - VBA
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 |
Working with Cell Comments - VBA
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 |
| All times are GMT +1. The time now is 02:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com