Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB ; How do I detect a Comment in a cell
I have a table where some cells have comments attached ("Insert" - "Comments"). As these comments are poorly visible, I am trying to lift the text in a comment box such that I can make it more visible - I want to show the text that is included in the comment of the selected cell in a designated worksheet cell (in this case the top left cell of the table) I can read the text in the comment through Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A2") = Mid(Target.Comment.Shape.AlternativeText, 10) end sub (I have included Mid(...,10) to get rid of the "TextBox : ") However - if a cell does NOT have a comment, the above gives an error. How can I ensure that does not block the rest of the code from running (ie more code under Worksheet_SelectionChange) Txs, RDWJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB ; How do I detect a Comment in a cell
You can use the following macros to show/hide all comments in the currently
acitve worksheet: Sub DisplayAllComments() Dim c As Comment Dim i As Integer Dim strNoComments As String strNoComments = "There were no comments found in the " & _ ActiveSheet.Name & " worksheet." For Each c In ActiveSheet.Comments i = i + 1 c.Visible = True Next c If i = 0 Then MsgBox strNoComments Set c = Nothing Exit Sub End Sub Sub HideAllComments() Dim c As Comment For Each c In ActiveSheet.Comments c.Visible = False Next c End Sub -- Kevin Backmann "rdwj" wrote: I have a table where some cells have comments attached ("Insert" - "Comments"). As these comments are poorly visible, I am trying to lift the text in a comment box such that I can make it more visible - I want to show the text that is included in the comment of the selected cell in a designated worksheet cell (in this case the top left cell of the table) I can read the text in the comment through Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A2") = Mid(Target.Comment.Shape.AlternativeText, 10) end sub (I have included Mid(...,10) to get rid of the "TextBox : ") However - if a cell does NOT have a comment, the above gives an error. How can I ensure that does not block the rest of the code from running (ie more code under Worksheet_SelectionChange) Txs, RDWJ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB ; How do I detect a Comment in a cell
Kevin,
I do not want to show or hide comments - I want to take the text out of the comment and place it in a cell..... I could use your basis to determine cell address of every comment, and based on that determine if the target cell has a comment, however, that takes a lot of calculating time..... Thanks for trying. Rob "Kevin B" wrote: You can use the following macros to show/hide all comments in the currently acitve worksheet: Sub DisplayAllComments() Dim c As Comment Dim i As Integer Dim strNoComments As String strNoComments = "There were no comments found in the " & _ ActiveSheet.Name & " worksheet." For Each c In ActiveSheet.Comments i = i + 1 c.Visible = True Next c If i = 0 Then MsgBox strNoComments Set c = Nothing Exit Sub End Sub Sub HideAllComments() Dim c As Comment For Each c In ActiveSheet.Comments c.Visible = False Next c End Sub -- Kevin Backmann "rdwj" wrote: I have a table where some cells have comments attached ("Insert" - "Comments"). As these comments are poorly visible, I am trying to lift the text in a comment box such that I can make it more visible - I want to show the text that is included in the comment of the selected cell in a designated worksheet cell (in this case the top left cell of the table) I can read the text in the comment through Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A2") = Mid(Target.Comment.Shape.AlternativeText, 10) end sub (I have included Mid(...,10) to get rid of the "TextBox : ") However - if a cell does NOT have a comment, the above gives an error. How can I ensure that does not block the rest of the code from running (ie more code under Worksheet_SelectionChange) Txs, RDWJ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB ; How do I detect a Comment in a cell
The following macro posts the comments found, starting in A1 of the currently
active worksheet. The integer variable i increments the offset value so that each subsequent comment is posted one cell below the other. Sub PostAllComments() Dim c As Comment Dim i As Integer Dim r As Range Set r = ActiveSheet.Range("A1") For Each c In ActiveSheet.Comments r.Offset(i).Value = c.Text i = i + 1 Next c Set c = Nothing Set r = nothing Exit Sub End Sub -- Kevin Backmann "rdwj" wrote: Kevin, I do not want to show or hide comments - I want to take the text out of the comment and place it in a cell..... I could use your basis to determine cell address of every comment, and based on that determine if the target cell has a comment, however, that takes a lot of calculating time..... Thanks for trying. Rob "Kevin B" wrote: You can use the following macros to show/hide all comments in the currently acitve worksheet: Sub DisplayAllComments() Dim c As Comment Dim i As Integer Dim strNoComments As String strNoComments = "There were no comments found in the " & _ ActiveSheet.Name & " worksheet." For Each c In ActiveSheet.Comments i = i + 1 c.Visible = True Next c If i = 0 Then MsgBox strNoComments Set c = Nothing Exit Sub End Sub Sub HideAllComments() Dim c As Comment For Each c In ActiveSheet.Comments c.Visible = False Next c End Sub -- Kevin Backmann "rdwj" wrote: I have a table where some cells have comments attached ("Insert" - "Comments"). As these comments are poorly visible, I am trying to lift the text in a comment box such that I can make it more visible - I want to show the text that is included in the comment of the selected cell in a designated worksheet cell (in this case the top left cell of the table) I can read the text in the comment through Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A2") = Mid(Target.Comment.Shape.AlternativeText, 10) end sub (I have included Mid(...,10) to get rid of the "TextBox : ") However - if a cell does NOT have a comment, the above gives an error. How can I ensure that does not block the rest of the code from running (ie more code under Worksheet_SelectionChange) Txs, RDWJ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB ; How do I detect a Comment in a cell
I neglected to say that the starting cell is cell A1 of the current workbook.
You can change this to whatever cell you want to start the list of comment text in. -- Kevin Backmann "rdwj" wrote: Kevin, I do not want to show or hide comments - I want to take the text out of the comment and place it in a cell..... I could use your basis to determine cell address of every comment, and based on that determine if the target cell has a comment, however, that takes a lot of calculating time..... Thanks for trying. Rob "Kevin B" wrote: You can use the following macros to show/hide all comments in the currently acitve worksheet: Sub DisplayAllComments() Dim c As Comment Dim i As Integer Dim strNoComments As String strNoComments = "There were no comments found in the " & _ ActiveSheet.Name & " worksheet." For Each c In ActiveSheet.Comments i = i + 1 c.Visible = True Next c If i = 0 Then MsgBox strNoComments Set c = Nothing Exit Sub End Sub Sub HideAllComments() Dim c As Comment For Each c In ActiveSheet.Comments c.Visible = False Next c End Sub -- Kevin Backmann "rdwj" wrote: I have a table where some cells have comments attached ("Insert" - "Comments"). As these comments are poorly visible, I am trying to lift the text in a comment box such that I can make it more visible - I want to show the text that is included in the comment of the selected cell in a designated worksheet cell (in this case the top left cell of the table) I can read the text in the comment through Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A2") = Mid(Target.Comment.Shape.AlternativeText, 10) end sub (I have included Mid(...,10) to get rid of the "TextBox : ") However - if a cell does NOT have a comment, the above gives an error. How can I ensure that does not block the rest of the code from running (ie more code under Worksheet_SelectionChange) Txs, RDWJ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB ; How do I detect a Comment in a cell
Have a look at Debra Dalgleish's site for some Dave Peterson code which places
comment text in an adjacent cell. http://www.contextures.com/xlcomment...l#CopyAdjacent Gord Dibben MS Excel MVP On Wed, 4 Jun 2008 09:54:03 -0700, rdwj wrote: Kevin, I do not want to show or hide comments - I want to take the text out of the comment and place it in a cell..... I could use your basis to determine cell address of every comment, and based on that determine if the target cell has a comment, however, that takes a lot of calculating time..... Thanks for trying. Rob "Kevin B" wrote: You can use the following macros to show/hide all comments in the currently acitve worksheet: Sub DisplayAllComments() Dim c As Comment Dim i As Integer Dim strNoComments As String strNoComments = "There were no comments found in the " & _ ActiveSheet.Name & " worksheet." For Each c In ActiveSheet.Comments i = i + 1 c.Visible = True Next c If i = 0 Then MsgBox strNoComments Set c = Nothing Exit Sub End Sub Sub HideAllComments() Dim c As Comment For Each c In ActiveSheet.Comments c.Visible = False Next c End Sub -- Kevin Backmann "rdwj" wrote: I have a table where some cells have comments attached ("Insert" - "Comments"). As these comments are poorly visible, I am trying to lift the text in a comment box such that I can make it more visible - I want to show the text that is included in the comment of the selected cell in a designated worksheet cell (in this case the top left cell of the table) I can read the text in the comment through Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A2") = Mid(Target.Comment.Shape.AlternativeText, 10) end sub (I have included Mid(...,10) to get rid of the "TextBox : ") However - if a cell does NOT have a comment, the above gives an error. How can I ensure that does not block the rest of the code from running (ie more code under Worksheet_SelectionChange) Txs, RDWJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To Detect A Negative Number In A Cell | Excel Worksheet Functions | |||
How to detect characters in cell? | Excel Discussion (Misc queries) | |||
How to detect characters with a cell? | Excel Discussion (Misc queries) | |||
Can I use a function to detect whether a cell is highlighted? | Excel Worksheet Functions | |||
Is there a function to detect changes in any given cell? | Excel Worksheet Functions |