Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Gord or other: dynamic comment
I think a macro published last year by Gord is going to meet my needs, but
with a minor adjustment. Show bellow is my current version of Gord's macro to load the text from a cell into the comment box of another cell. It works the first time, but I need to be able to run the macro numerous times to reload different content into the comment box. The destination cells stay the same - they are actually formulas that pull in data based on an indirect reference to another sheet, based on a row number entered by the user. Also, I need to make the comment boxes huge; if I delete them each time they get reset to the default (tiny). TIA My verson of Gord Dibben's macro: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4") Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text End If Next r End Sub (with thanks to Gord Dibben MS Excel MVP) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Gord or other: dynamic comment
Sub UpdateComments()
' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 'adjust to suit .Shape.Height = 200 'adjust to suit End With End If Next r End Sub Adjust Width and Height to numbers you like. Note: this can be run from a Worksheet_Calculate event if that's what you want. Right-click on the sheet tab and "View Code". Copy/paste this into that sheet module. Private Sub Worksheet_Calculate() Call UpdateComments End Sub Gord On Wed, 25 Feb 2009 13:20:05 -0800, andy62 wrote: I think a macro published last year by Gord is going to meet my needs, but with a minor adjustment. Show bellow is my current version of Gord's macro to load the text from a cell into the comment box of another cell. It works the first time, but I need to be able to run the macro numerous times to reload different content into the comment box. The destination cells stay the same - they are actually formulas that pull in data based on an indirect reference to another sheet, based on a row number entered by the user. Also, I need to make the comment boxes huge; if I delete them each time they get reset to the default (tiny). TIA My verson of Gord Dibben's macro: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4") Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text End If Next r End Sub (with thanks to Gord Dibben MS Excel MVP) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Gord or other: dynamic comment
Thanks Gord, it works great. But I have done something that is causing
really long text to get cut off. Can you see the issue? The macro copies in content from cells 50 rows down (your Offset function). Those cells actually house formulas like this that dynamically grab text from another worksheet: =INDIRECT("RowToCopy!BT"&B3) The text in the source cell (RowToCopy!BT12, for instance) might have 3000 characters. The formula above seems to display only a portion of those characters, but I was hoping the whole text was available even though not displayed. But then running the macro copies even less of the text into the comment box. Am I hitting up against some laws of physics here, working with too many characters in a cell? Since I am Looking at only 4 cells, should I skip the middle step and, inside the macro, somehow refer directly to those source cells on the other worksheet? TIA "Gord Dibben" wrote: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4,D13") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 'adjust to suit .Shape.Height = 200 'adjust to suit End With End If Next r End Sub Adjust Width and Height to numbers you like. Note: this can be run from a Worksheet_Calculate event if that's what you want. Right-click on the sheet tab and "View Code". Copy/paste this into that sheet module. Private Sub Worksheet_Calculate() Call UpdateComments End Sub Gord On Wed, 25 Feb 2009 13:20:05 -0800, andy62 wrote: I think a macro published last year by Gord is going to meet my needs, but with a minor adjustment. Show bellow is my current version of Gord's macro to load the text from a cell into the comment box of another cell. It works the first time, but I need to be able to run the macro numerous times to reload different content into the comment box. The destination cells stay the same - they are actually formulas that pull in data based on an indirect reference to another sheet, based on a row number entered by the user. Also, I need to make the comment boxes huge; if I delete them each time they get reset to the default (tiny). TIA My verson of Gord Dibben's macro: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4") Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text End If Next r End Sub (with thanks to Gord Dibben MS Excel MVP) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Gord or other: dynamic comment
I have tested the macro with 3000 chars in the source cells using Excel 2003
Excel cuts off the cell Comment text at 1024 chars, which is the viewable number of chars in a cell, even though 3000 are visible in the formula bar. This viewable limit is a built-in limit of Excel 2003 and older. See "specifications" in help. So..........referring to source cells on other sheet will not get you any more chars. I believe Excel 2007 will show about 8000 chars but I can't find a number in 2007 help. Perhaps someone more conversant with 2007 can confirm that number? In 2007 I placed 4000 chars in a cell..............the macro entered all 4000 into the Comment. Gord On Thu, 26 Feb 2009 07:05:03 -0800, andy62 wrote: Thanks Gord, it works great. But I have done something that is causing really long text to get cut off. Can you see the issue? The macro copies in content from cells 50 rows down (your Offset function). Those cells actually house formulas like this that dynamically grab text from another worksheet: =INDIRECT("RowToCopy!BT"&B3) The text in the source cell (RowToCopy!BT12, for instance) might have 3000 characters. The formula above seems to display only a portion of those characters, but I was hoping the whole text was available even though not displayed. But then running the macro copies even less of the text into the comment box. Am I hitting up against some laws of physics here, working with too many characters in a cell? Since I am Looking at only 4 cells, should I skip the middle step and, inside the macro, somehow refer directly to those source cells on the other worksheet? TIA "Gord Dibben" wrote: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4,D13") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 'adjust to suit .Shape.Height = 200 'adjust to suit End With End If Next r End Sub Adjust Width and Height to numbers you like. Note: this can be run from a Worksheet_Calculate event if that's what you want. Right-click on the sheet tab and "View Code". Copy/paste this into that sheet module. Private Sub Worksheet_Calculate() Call UpdateComments End Sub Gord On Wed, 25 Feb 2009 13:20:05 -0800, andy62 wrote: I think a macro published last year by Gord is going to meet my needs, but with a minor adjustment. Show bellow is my current version of Gord's macro to load the text from a cell into the comment box of another cell. It works the first time, but I need to be able to run the macro numerous times to reload different content into the comment box. The destination cells stay the same - they are actually formulas that pull in data based on an indirect reference to another sheet, based on a row number entered by the user. Also, I need to make the comment boxes huge; if I delete them each time they get reset to the default (tiny). TIA My verson of Gord Dibben's macro: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4") Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text End If Next r End Sub (with thanks to Gord Dibben MS Excel MVP) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Gord or other: dynamic comment
Thanks for investigating, Gord. I wonder if assigning the text in a
different way would help? When I go to the source cell, copy the text itself (not just copy the cell but go into edit more and copy the contents) and then paste the contents into the same comments box, I get all 3000 characters. Does the macro copy the text in this way, and if not, could it? Andy "Gord Dibben" wrote: I have tested the macro with 3000 chars in the source cells using Excel 2003 Excel cuts off the cell Comment text at 1024 chars, which is the viewable number of chars in a cell, even though 3000 are visible in the formula bar. This viewable limit is a built-in limit of Excel 2003 and older. See "specifications" in help. So..........referring to source cells on other sheet will not get you any more chars. I believe Excel 2007 will show about 8000 chars but I can't find a number in 2007 help. Perhaps someone more conversant with 2007 can confirm that number? In 2007 I placed 4000 chars in a cell..............the macro entered all 4000 into the Comment. Gord On Thu, 26 Feb 2009 07:05:03 -0800, andy62 wrote: Thanks Gord, it works great. But I have done something that is causing really long text to get cut off. Can you see the issue? The macro copies in content from cells 50 rows down (your Offset function). Those cells actually house formulas like this that dynamically grab text from another worksheet: =INDIRECT("RowToCopy!BT"&B3) The text in the source cell (RowToCopy!BT12, for instance) might have 3000 characters. The formula above seems to display only a portion of those characters, but I was hoping the whole text was available even though not displayed. But then running the macro copies even less of the text into the comment box. Am I hitting up against some laws of physics here, working with too many characters in a cell? Since I am Looking at only 4 cells, should I skip the middle step and, inside the macro, somehow refer directly to those source cells on the other worksheet? TIA "Gord Dibben" wrote: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4,D13") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 'adjust to suit .Shape.Height = 200 'adjust to suit End With End If Next r End Sub Adjust Width and Height to numbers you like. Note: this can be run from a Worksheet_Calculate event if that's what you want. Right-click on the sheet tab and "View Code". Copy/paste this into that sheet module. Private Sub Worksheet_Calculate() Call UpdateComments End Sub Gord On Wed, 25 Feb 2009 13:20:05 -0800, andy62 wrote: I think a macro published last year by Gord is going to meet my needs, but with a minor adjustment. Show bellow is my current version of Gord's macro to load the text from a cell into the comment box of another cell. It works the first time, but I need to be able to run the macro numerous times to reload different content into the comment box. The destination cells stay the same - they are actually formulas that pull in data based on an indirect reference to another sheet, based on a row number entered by the user. Also, I need to make the comment boxes huge; if I delete them each time they get reset to the default (tiny). TIA My verson of Gord Dibben's macro: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4") Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text End If Next r End Sub (with thanks to Gord Dibben MS Excel MVP) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Gord or other: dynamic comment
This revision will put all 3000 chars in a Comment in XL2003
It basically copies the text from the formula bar. Sub UpdateComments22() Dim cmt As Comment Dim rtext As String Dim r As Range For Each r In Range("D4:F4,D13") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment rtext = r.Offset(50, 0).Value cmt.Text Text:=rtext With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 600 'adjust to suit .Shape.Height = 600 'adjust to suit End With End If Next r End Sub Gord On Sun, 1 Mar 2009 13:45:01 -0800, andy62 wrote: Thanks for investigating, Gord. I wonder if assigning the text in a different way would help? When I go to the source cell, copy the text itself (not just copy the cell but go into edit more and copy the contents) and then paste the contents into the same comments box, I get all 3000 characters. Does the macro copy the text in this way, and if not, could it? Andy "Gord Dibben" wrote: I have tested the macro with 3000 chars in the source cells using Excel 2003 Excel cuts off the cell Comment text at 1024 chars, which is the viewable number of chars in a cell, even though 3000 are visible in the formula bar. This viewable limit is a built-in limit of Excel 2003 and older. See "specifications" in help. So..........referring to source cells on other sheet will not get you any more chars. I believe Excel 2007 will show about 8000 chars but I can't find a number in 2007 help. Perhaps someone more conversant with 2007 can confirm that number? In 2007 I placed 4000 chars in a cell..............the macro entered all 4000 into the Comment. Gord On Thu, 26 Feb 2009 07:05:03 -0800, andy62 wrote: Thanks Gord, it works great. But I have done something that is causing really long text to get cut off. Can you see the issue? The macro copies in content from cells 50 rows down (your Offset function). Those cells actually house formulas like this that dynamically grab text from another worksheet: =INDIRECT("RowToCopy!BT"&B3) The text in the source cell (RowToCopy!BT12, for instance) might have 3000 characters. The formula above seems to display only a portion of those characters, but I was hoping the whole text was available even though not displayed. But then running the macro copies even less of the text into the comment box. Am I hitting up against some laws of physics here, working with too many characters in a cell? Since I am Looking at only 4 cells, should I skip the middle step and, inside the macro, somehow refer directly to those source cells on the other worksheet? TIA "Gord Dibben" wrote: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4,D13") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 'adjust to suit .Shape.Height = 200 'adjust to suit End With End If Next r End Sub Adjust Width and Height to numbers you like. Note: this can be run from a Worksheet_Calculate event if that's what you want. Right-click on the sheet tab and "View Code". Copy/paste this into that sheet module. Private Sub Worksheet_Calculate() Call UpdateComments End Sub Gord On Wed, 25 Feb 2009 13:20:05 -0800, andy62 wrote: I think a macro published last year by Gord is going to meet my needs, but with a minor adjustment. Show bellow is my current version of Gord's macro to load the text from a cell into the comment box of another cell. It works the first time, but I need to be able to run the macro numerous times to reload different content into the comment box. The destination cells stay the same - they are actually formulas that pull in data based on an indirect reference to another sheet, based on a row number entered by the user. Also, I need to make the comment boxes huge; if I delete them each time they get reset to the default (tiny). TIA My verson of Gord Dibben's macro: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4") Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text End If Next r End Sub (with thanks to Gord Dibben MS Excel MVP) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Gord or other: dynamic comment
Thanks, Gord for your diligence. It works great! Since the cells with the
content were actually formulas, I added some simple code that copies all of them down one row, as Values, to produce the actual content in the formula bar each time. "Gord Dibben" wrote: This revision will put all 3000 chars in a Comment in XL2003 It basically copies the text from the formula bar. Sub UpdateComments22() Dim cmt As Comment Dim rtext As String Dim r As Range For Each r In Range("D4:F4,D13") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment rtext = r.Offset(50, 0).Value cmt.Text Text:=rtext With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 600 'adjust to suit .Shape.Height = 600 'adjust to suit End With End If Next r End Sub Gord On Sun, 1 Mar 2009 13:45:01 -0800, andy62 wrote: Thanks for investigating, Gord. I wonder if assigning the text in a different way would help? When I go to the source cell, copy the text itself (not just copy the cell but go into edit more and copy the contents) and then paste the contents into the same comments box, I get all 3000 characters. Does the macro copy the text in this way, and if not, could it? Andy "Gord Dibben" wrote: I have tested the macro with 3000 chars in the source cells using Excel 2003 Excel cuts off the cell Comment text at 1024 chars, which is the viewable number of chars in a cell, even though 3000 are visible in the formula bar. This viewable limit is a built-in limit of Excel 2003 and older. See "specifications" in help. So..........referring to source cells on other sheet will not get you any more chars. I believe Excel 2007 will show about 8000 chars but I can't find a number in 2007 help. Perhaps someone more conversant with 2007 can confirm that number? In 2007 I placed 4000 chars in a cell..............the macro entered all 4000 into the Comment. Gord On Thu, 26 Feb 2009 07:05:03 -0800, andy62 wrote: Thanks Gord, it works great. But I have done something that is causing really long text to get cut off. Can you see the issue? The macro copies in content from cells 50 rows down (your Offset function). Those cells actually house formulas like this that dynamically grab text from another worksheet: =INDIRECT("RowToCopy!BT"&B3) The text in the source cell (RowToCopy!BT12, for instance) might have 3000 characters. The formula above seems to display only a portion of those characters, but I was hoping the whole text was available even though not displayed. But then running the macro copies even less of the text into the comment box. Am I hitting up against some laws of physics here, working with too many characters in a cell? Since I am Looking at only 4 cells, should I skip the middle step and, inside the macro, somehow refer directly to those source cells on the other worksheet? TIA "Gord Dibben" wrote: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4,D13") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 'adjust to suit .Shape.Height = 200 'adjust to suit End With End If Next r End Sub Adjust Width and Height to numbers you like. Note: this can be run from a Worksheet_Calculate event if that's what you want. Right-click on the sheet tab and "View Code". Copy/paste this into that sheet module. Private Sub Worksheet_Calculate() Call UpdateComments End Sub Gord On Wed, 25 Feb 2009 13:20:05 -0800, andy62 wrote: I think a macro published last year by Gord is going to meet my needs, but with a minor adjustment. Show bellow is my current version of Gord's macro to load the text from a cell into the comment box of another cell. It works the first time, but I need to be able to run the macro numerous times to reload different content into the comment box. The destination cells stay the same - they are actually formulas that pull in data based on an indirect reference to another sheet, based on a row number entered by the user. Also, I need to make the comment boxes huge; if I delete them each time they get reset to the default (tiny). TIA My verson of Gord Dibben's macro: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4") Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text End If Next r End Sub (with thanks to Gord Dibben MS Excel MVP) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
For Gord or other: dynamic comment
With the revised code I have no problem getting all text into the Comments
even when the source cells are formulas. On Sheet2 I have a formula in A1 =REPT("qwerty ",500) which gives me 3500 characters. On Sheet1 I have a formula =Sheet2!A1 I use this as source cell for the Comment I don't see a need for the copy as values step. But good to hear you're sorted out. Gord On Mon, 2 Mar 2009 13:38:01 -0800, andy62 wrote: Thanks, Gord for your diligence. It works great! Since the cells with the content were actually formulas, I added some simple code that copies all of them down one row, as Values, to produce the actual content in the formula bar each time. "Gord Dibben" wrote: This revision will put all 3000 chars in a Comment in XL2003 It basically copies the text from the formula bar. Sub UpdateComments22() Dim cmt As Comment Dim rtext As String Dim r As Range For Each r In Range("D4:F4,D13") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment rtext = r.Offset(50, 0).Value cmt.Text Text:=rtext With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 600 'adjust to suit .Shape.Height = 600 'adjust to suit End With End If Next r End Sub Gord On Sun, 1 Mar 2009 13:45:01 -0800, andy62 wrote: Thanks for investigating, Gord. I wonder if assigning the text in a different way would help? When I go to the source cell, copy the text itself (not just copy the cell but go into edit more and copy the contents) and then paste the contents into the same comments box, I get all 3000 characters. Does the macro copy the text in this way, and if not, could it? Andy "Gord Dibben" wrote: I have tested the macro with 3000 chars in the source cells using Excel 2003 Excel cuts off the cell Comment text at 1024 chars, which is the viewable number of chars in a cell, even though 3000 are visible in the formula bar. This viewable limit is a built-in limit of Excel 2003 and older. See "specifications" in help. So..........referring to source cells on other sheet will not get you any more chars. I believe Excel 2007 will show about 8000 chars but I can't find a number in 2007 help. Perhaps someone more conversant with 2007 can confirm that number? In 2007 I placed 4000 chars in a cell..............the macro entered all 4000 into the Comment. Gord On Thu, 26 Feb 2009 07:05:03 -0800, andy62 wrote: Thanks Gord, it works great. But I have done something that is causing really long text to get cut off. Can you see the issue? The macro copies in content from cells 50 rows down (your Offset function). Those cells actually house formulas like this that dynamically grab text from another worksheet: =INDIRECT("RowToCopy!BT"&B3) The text in the source cell (RowToCopy!BT12, for instance) might have 3000 characters. The formula above seems to display only a portion of those characters, but I was hoping the whole text was available even though not displayed. But then running the macro copies even less of the text into the comment box. Am I hitting up against some laws of physics here, working with too many characters in a cell? Since I am Looking at only 4 cells, should I skip the middle step and, inside the macro, somehow refer directly to those source cells on the other worksheet? TIA "Gord Dibben" wrote: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4,D13") r.ClearComments Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text With r.Comment .Shape.TextFrame.AutoSize = True lArea = .Shape.Width * .Shape.Height .Shape.Width = 200 'adjust to suit .Shape.Height = 200 'adjust to suit End With End If Next r End Sub Adjust Width and Height to numbers you like. Note: this can be run from a Worksheet_Calculate event if that's what you want. Right-click on the sheet tab and "View Code". Copy/paste this into that sheet module. Private Sub Worksheet_Calculate() Call UpdateComments End Sub Gord On Wed, 25 Feb 2009 13:20:05 -0800, andy62 wrote: I think a macro published last year by Gord is going to meet my needs, but with a minor adjustment. Show bellow is my current version of Gord's macro to load the text from a cell into the comment box of another cell. It works the first time, but I need to be able to run the macro numerous times to reload different content into the comment box. The destination cells stay the same - they are actually formulas that pull in data based on an indirect reference to another sheet, based on a row number entered by the user. Also, I need to make the comment boxes huge; if I delete them each time they get reset to the default (tiny). TIA My verson of Gord Dibben's macro: Sub UpdateComments() ' ' UpdateComments Macro ' Macro recorded 02/25/2009 ' Dim cmt As Comment Dim r As Range For Each r In Range("D4:F4") Set cmt = r.Comment If cmt Is Nothing Then Set cmt = r.AddComment cmt.Text Text:=r.Offset(50, 0).Text End If Next r End Sub (with thanks to Gord Dibben MS Excel MVP) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Navigation Toolbar help- Gord Dibben | Excel Discussion (Misc queries) | |||
Filter or sumproduct (Question for Gord Dibben) | Excel Discussion (Misc queries) | |||
Question for Gord D | Excel Discussion (Misc queries) | |||
A question for Gord Dibben | Excel Discussion (Misc queries) | |||
a comment plugin & copy paste directly from excel to comment ? fr. | Excel Worksheet Functions |