Display the TEXT of a comment in a new cell

Is there a way to display the comment, attached to a cell, as the
'value' of the cell?

I have copied/pasted the comments to a new form (sheet) and would like
to have the comment text displayed IN the new cell.  Then I can print
the sheet.

My current macro copies all comments from one sheet and pastes them
onto a second sheet.  Now the new sheet shows the comment indicator,
with a blank cell.  I want to replace the blank cell, with the TEXT of
the comment.  Can this be done?

Thank you in advance.

JO

0
wojo36 (48)
7/6/2005 1:28:03 AM
excel 39879 articles. 2 followers. Follow

19 Replies
916 Views

Similar Articles

[PageSpeed] 5

Hi wojo,

You could try this user-defined function placing it in a standard module of 
your workbook:

Function GetCommentText(myCell As Range) As String
    On Error Resume Next
    GetCommentText = myCell.Cells(1, 1).Comment.Text
End Function

and then use the following formula on the spreadsheet:

=GetCommentText(A1)

where A1 is the cell which has the comment to copy.

Regards,
KL


"wojo" <wojo36@yahoo.com> wrote in message 
news:1120613283.305722.165220@g14g2000cwa.googlegroups.com...
> Is there a way to display the comment, attached to a cell, as the
> 'value' of the cell?
>
> I have copied/pasted the comments to a new form (sheet) and would like
> to have the comment text displayed IN the new cell.  Then I can print
> the sheet.
>
> My current macro copies all comments from one sheet and pastes them
> onto a second sheet.  Now the new sheet shows the comment indicator,
> with a blank cell.  I want to replace the blank cell, with the TEXT of
> the comment.  Can this be done?
>
> Thank you in advance.
>
> JO
> 


0
7/6/2005 1:37:29 AM
I'm fairly 'self taught' in Excel.  I will digest your solution, give
it a try and respond here.  Thanks in advance, wish me luck.

0
wojo36 (48)
7/6/2005 1:41:37 AM
Here's what I entered in the module.  I get an "error"

Fuction GetCommentText(myCell As Range)As String
On Error Resume Next
    GetCommentText = myCell.Cells(1, 1).Comment.Text
    End Function

Am I supposed to replace anything above, with "my" spreadsheet info?

Jo

0
wojo36 (48)
7/6/2005 4:20:42 PM
Hi wojo,

Try replacing "Fuction" by "Function" in the very first line.

Regards,
KL


"wojo" <wojo36@yahoo.com> wrote in message 
news:1120666842.098251.107290@g47g2000cwa.googlegroups.com...
> Here's what I entered in the module.  I get an "error"
>
> Fuction GetCommentText(myCell As Range)As String
> On Error Resume Next
>    GetCommentText = myCell.Cells(1, 1).Comment.Text
>    End Function
>
> Am I supposed to replace anything above, with "my" spreadsheet info?
>
> Jo
> 


0
7/6/2005 4:33:56 PM
I still get the error, "Compile error Expected list seperator or )" ,
after I fixed my typo.  Jo

0
wojo36 (48)
7/7/2005 4:08:01 PM
Hi wojo,

I am not sure what else could cause the error you get. I copied the code 
from your message, corrected the word Function and it works well. Try maybe 
do the same copy-paste directly from the message.

Regards,
KL

"wojo" <wojo36@yahoo.com> wrote in message 
news:1120752481.502141.100380@g14g2000cwa.googlegroups.com...
>I still get the error, "Compile error Expected list seperator or )" ,
> after I fixed my typo.  Jo
> 


0
7/7/2005 4:26:51 PM

-- 
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"wojo" <wojo36@yahoo.com> wrote in message news:1120752481.502141.100380@g14g2000cwa.googlegroups.com...
> I still get the error, "Compile error Expected list seperator or )" ,
> after I fixed my typo.  Jo
>


0
dmcritchie (2586)
7/7/2005 9:48:50 PM
sorry didn't mean to send anything,
I can't duplicate your error, even tried putting in a comma or a semicolon
to separate non-existent additional arguments.

The function presented
works for me, check that you only have a cell reference in your
invocation.      =GetCommentText(A14)
     =personal.xls!GetCommentText(A14)
that it was installed as a regular macro   i.e.  Module1
which could be renamed later, but not one originally named  like  Class1


0
dmcritchie (2586)
7/7/2005 11:15:48 PM
The comment is "attached" to cell F43.  I put the function in Module 1,
then put

=GetCommentText(F43)

In cell F43.   Is that where it goes?  I don't get an error now, but I
don't get the text of the attached comment as the Value of F43.

I'm sure it's something silly, but I still need more guidance.  Thanks
everyone.

Jo

0
wojo36 (48)
7/8/2005 12:07:03 PM
Are you sure that F43 actually contains a comment?

If you hit Insert (on the menubar), do you see "Edit Comment" or "Insert
Comment".

If you see "insert comment", then that cell doesn't have a comment--maybe it's
the data|validation input message.

If you do see "edit comment", try hitting F2 and then enter (with F43 selected).

Did that help?

If that's the case, I'd modify the function slightly:

Function GetCommentText(myCell As Range) As String
    application.volatile  '<---
    On Error Resume Next
    GetCommentText = myCell.Cells(1, 1).Comment.Text
End Function

Changing comments won't cause the GetCommentText function to reevaluate.  By
pretending to change the cell, xl saw that the cell "changed" and reevaluated
the function.

By adding "application volatile", this function will reevaluate whenever excel
recalculates--so it could be one recalc behind.


wojo wrote:
> 
> The comment is "attached" to cell F43.  I put the function in Module 1,
> then put
> 
> =GetCommentText(F43)
> 
> In cell F43.   Is that where it goes?  I don't get an error now, but I
> don't get the text of the attached comment as the Value of F43.
> 
> I'm sure it's something silly, but I still need more guidance.  Thanks
> everyone.
> 
> Jo

-- 

Dave Peterson
0
ec357201 (5290)
7/8/2005 12:19:42 PM
I think I figured out the problem.  I am entering the =GetComment line
in the same cell as the cell with the attached comment.  Can I do this,
or does it need to go into a different cell?

Jo

0
wojo36 (48)
7/8/2005 12:27:31 PM
It worked in the same cell.

I think it's a sequencing problem--when you change the comment and when excel
knows to recalc the formula.



wojo wrote:
> 
> I think I figured out the problem.  I am entering the =GetComment line
> in the same cell as the cell with the attached comment.  Can I do this,
> or does it need to go into a different cell?
> 
> Jo

-- 

Dave Peterson
0
ec357201 (5290)
7/8/2005 12:44:44 PM
I'm currently away from my computer, so I can't try your solution until
Monday evening.  I will let you know what I discover.  Jo

0
wojo36 (48)
7/9/2005 5:19:16 PM
Ok, Don't know why, but the Function is working now.  Now, if only I
could LINK the comment.

Right now, the comment is on one sheet and I copy/paste
special/comments (using a macro) to a Form.  Then I have another macro
that sorts the information.

One last question, regarding the Comments.
Is there a way to show the TEXT of the comment, without the "personal
copy" that is 'attached' to the comment.  I believe if I was at work,
the "   " would be the name of the user that entered the information.
This part of the comment is not necessary.  I have a small amount of
room to paste the text of the comment and would like to also limit the
size of the comment to 25 characters.

Can this be done?

Thanks to all that helped with this problem.

Jo

0
wojo36 (48)
7/12/2005 11:23:07 AM
Sorry, I forgot to mention, I would still like the 'attached' "name" to
be present in the comment, but would like to have it deleted on the
Form, where I extracted the text and placed it into the cell.

Example:  Comment
 Personal Copy:

I want the cell to display Only the information after the : (semi
colon)

Everyone has been so helpful, I hope I'm not asking too much.

Jo

0
wojo36 (48)
7/12/2005 11:57:26 AM
From your earlier reply...

I'm not sure what Link means the way you use it.

But this is one way to ignore the stuff before the colon:

Option Explicit
Function GetCommentText(FCell As Range) As Variant
    Application.Volatile
    
    Dim ColonPos As Long
    Dim myStr As String
    
    Set FCell = FCell(1)
    
    If FCell.Comment Is Nothing Then
        GetCommentText = ""
    Else
        myStr = FCell.Comment.Text
        ColonPos = InStr(1, myStr, ":")
        myStr = Trim(Mid(myStr, ColonPos + 1))
        If Left(myStr, 1) = vbLf Then
            myStr = Mid(myStr, 2)
        End If
        GetCommentText = myStr
    End If
    
End Function

wojo wrote:
> 
> Sorry, I forgot to mention, I would still like the 'attached' "name" to
> be present in the comment, but would like to have it deleted on the
> Form, where I extracted the text and placed it into the cell.
> 
> Example:  Comment
>  Personal Copy:
> 
> I want the cell to display Only the information after the : (semi
> colon)
> 
> Everyone has been so helpful, I hope I'm not asking too much.
> 
> Jo

-- 

Dave Peterson
0
petersod (12005)
7/12/2005 12:13:36 PM
Finally, I got this to work.  I'm not really sure how, I didn't change
anything.  Maybe it just 'happened' when I put my cursor back in the
original cell with the comment and then entered.  The 'text' of the
comment is now right where I want it to be.

Now, if only Excel would allow us to 'link' comments, along with a
cell.  That would be great.  Right now, I am playing with macros to
copy/paste special, to the new form, before the user prints.

Thanks bunches to all that helped.

Jo

0
wojo36 (48)
7/18/2005 3:22:48 AM
The UDF could be one calculation cycle behind.

Changing the comment doesn't make the function recalculate.  But if you've added
that "application.volatile" line, then whenever excel calculates, your UDF will
be recalced.

But by making a change to the cell (even just F2, then enter), excel thought
that the UDF should recalc.

wojo wrote:
> 
> Finally, I got this to work.  I'm not really sure how, I didn't change
> anything.  Maybe it just 'happened' when I put my cursor back in the
> original cell with the comment and then entered.  The 'text' of the
> comment is now right where I want it to be.
> 
> Now, if only Excel would allow us to 'link' comments, along with a
> cell.  That would be great.  Right now, I am playing with macros to
> copy/paste special, to the new form, before the user prints.
> 
> Thanks bunches to all that helped.
> 
> Jo

-- 

Dave Peterson
0
petersod (12005)
7/18/2005 11:28:56 AM
I changed the function, to your code above.  This does a wonderful job
of deleting the user's name and the colon -THANKS!!!!!!

This particular worksheet was created for printing information only.
There are no calculations, The good news is, I have a button on the
main worksheet, this button, updates the information on the form and
does the same thing as calculating.... the comments ARE updated (thanks
to you and others.)

BTW... this is the same form that I am trying to sort.  I'm still
working on that.

What would we do without helpers like you?  I shudder to think!

Jo

0
wojo36 (48)
7/21/2005 4:43:29 PM
Reply:

Similar Artilces:

Multiple Sections of a Circle Shape/text box to follow circle patt
Help! 2 part question, and I've been to Help, Google, and MVP blogs for hours and can't find an answer. As a Visio 11 Standard (2003?) user newbie, I'm having trouble resizing and aligning the 'partial' shapes of a circle, when attempting to make a circle with more than 4 sections. (Partial circles work well when you have four distinct sections, as the rotate/flip function is adequate.) My frustration is mounting, as I'm dealing with these sections as part of an 'onion' template of concentric circles, and I've now got sections for each circle all...

Incrementing named cells
Merry Christmas everyone!!! Was wondering if any of you can help me with this. I have a financial model that currently has numerous named cells in the format of XXX_03 ( the 03 is to denote 2003 ) but I am making 2004 projection and therefore am wondering if there was an easy way to sort of copy and paste the names into a new column and automically renaming cells by an increment of 1 to XXX_04. If anyone can help, it will be greatly appreciated! Thank you Justin See one answer 1 minute later -- Don Guillett SalesAid Software donaldb@281.com "Justin" <jsim_hba2003@hot...

Using PERCENTILE with *specific* cells in a column
I am trying to use the PERCENTILE function with specific cells in a column. For example, lets say the column is: A 1 5 2 6 3 7 4 8 Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 and A4. I can achieve this by defining a named range such that it would be equal to A1 and A4, but would rather not use named ranges, as the above is a very simplistic example of what I want to do -- using named ranges would require a substantial amount of effort to achieve my end goal. I also realize I could re-order the rows such that I swap A4 with A2, therefore could use perc...

New Chart in Excel 2007 is NOT showed unless preview
I have a sheet with multiple charts in Excel 2007 one of the new created charts is hidden when I open the file Unless preview is selected, then finally turned on BUT many formats are changed and objects on charts (titles,...) can not be moved ! what's wrong ? Make sure you have installed SP2 for Office 2007 as this fixes some (not all) chart problems best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rolando" <Rolando@discussions.microsoft.com> wrote in message news:0B0A3E5C-AABD-4982-962B-8C5C09A2CC5A@microsoft...

Customized display order for fields
I need to define formats (i.e. bold, underline) for reference table fields. For example, let’s say reference table A contains three fields: Field1, Field2, Field3 and two rows. Reference Table A ID _Num Field1 Field2 Field3 1 Single Double Triple 2 Single Double Quadruple Forms need to know that for row 1 Double will be in bold and Triple will be underlined. However, for row 2, only Quadruple need to display in bold on forms, reports, etc. In the example above, what if I wanted the first row to be displayed on a form as Triple, Single, Double and the second row to be...

Wrap Text in Merged Cell
Excel 2007 If I merge several (or even 2) cells in a row, I can not get the text to wrap when I hit enter. It works, of course, in a single cell. How can I get the text to wrap in merged cells? I have checked "Wrap text" in the Format Cells dialog box, Thanks, Bob Atkinson Long audible sigh here................. One more victim of "merged cells". Wrap Text works fine on merged cells, but Autofit does not work. You need VBA event code to do that. Here is code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cW...

I get wrong dates when i paste from a different sheet into a new s
Hello again, My problem now is when I open an old excel sheet with data on it and try to paste that info on a new sheet I get wrong information. example: I'm trying to copy dates from an excel sheet I have. The dates are for the year 2004. When I copy the data and paste it on a new sheet the dates I now see are 2000. I'm puzzled. What I find wierd is when I have clipboard open the information shows the correct dates but when it's actually pasted on the sheet it shows as 2000. I'm using Excel 2003, but I get the same thing when using excel 2002(xp) Thanks for your help,...

New users loose SMTP address
Scenario: W2k3, Exhange2k3 2 domain servers create a user on either domain controller, add the smtp address we want (from a large list), come back a few minutes later and the address will change to the system default. Notes; we dont run the smtp recipient policies as such - just a general one with all our smtp address suffixes, this problem only occurs on every four/five accounts created. At first we thought it was the individual support person, then blamed his PC, his MMC connection, now Tech servoces have found it occurrs to anyone creating large numbers of users. Thought it could be...

Month display in Microsoft Outlook 2003's Calendar
When using Microsoft Outlook 2003's Calendar in Month view, Saturday and Sunday are combined as one square (yes, they do have the date numbers and a line between them). This may be convenient for people using Outlook only for business purposes, but if someone were to have a job that involved weekends, or was not using Outlook for business purposes at all (which is my case), this could be extremely annoying. I would like to know if there is a way to display the Month view as the standard Sunday on the left and Saturday on the right, with Monday through Friday in between. I want all d...

Tab into specified cells
I'm looking to set up a worksheet so that when I open it up I can only tab into specific cells. Is there anyway of doing this? Thanks There are a few way. One is to unlock the cells you want to tab into (Format>Cells>Protection), and then lock the sheet (Tools>Protection>Protect Sheet) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "sho" <anonymous@discussions.microsoft.com> wrote in message news:644601c47543$7c069000$a401280a@phx.gbl... > I'm looking to set ...

New Email addresses
I am looking for a way to change all of my users email addresses to use one standard naming convention. Currently we have a wide mix of formats. I would like to settle on firstname.lastname@domain.com Is there a tool or utility that I can use to force a change and make the new ones the default address as well as keeping the original address. thanks LDIFDE will do this. You could also add the new address to your recipient policy and force it to run, but that might have side effects you don't like. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!...

Adding new account to same bank
In Money, I have an checking account set up for a particular bank. I have since opened a new credit card account with the same bank. When I try add this account via. Account List- Add a New account - Choose account type (select credit card) - (Choose the bank) - I get the message "Your account list already contains accounts with (particular banks name). After Money updates your accounts, review the account list to see if it shows duplicate copies of any accounts... click "OK". If I click OK Money changes the exiting accounts type to a credit card account instead of adding...

Find all cells with a number and mulitply
How can I find all cells in a sheet that are a number greater than zero and multiply each one by a number? IE: the number to multiply by is 1.2 A1 Tom A2 25 A3 0 G12 Bob G13 25 I want to programatically (without using a macro) find cells a2 and g13 only and multiply them by 1.2 Thanks in advance for your help. Jim Jim, As long as you don't have any negative numbers, it doesn't matter about being > 0 (0*n=0) or being text. Try this Put 1.2 in a spare cell and then copy it Select all the cells Goto menu Edit>Pastespecial Click the Multiply option OK out Now clear the c...

New Calendar event only showing as all day tas
When I create a new calendar event, say for an hour. It creates itself as an all day event. (Outlook 2002) If I view the event in OWA the event shows properly as the one hour event. If I view the event and look at the scheduling tab, the event shows properly Sometimes the event will disappear in Outlook, but I can still see it using OWA. The users mailbox has been deleted and recreated (mail reimported using a pst file). This problem can be recreated on a different workstation. New profile did not fix problem. Thanks ...

Cell comments
I have already created the code using VBA to pull the data from the M Access table. Is there a way to set it up so that when you pass ove the cell it will automatically run my routine -- LA ----------------------------------------------------------------------- LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=965 View this thread: http://www.excelforum.com/showthread.php?threadid=26920 Hi not possible AFAIK as there's no event which got triggered by just moving over a comment field -- Regards Frank Kabel Frankfurt, Germany LAF wrote: > I have alr...

Merging cells #4
I am using an existing worksheet in Excel. Some cells have already been merged. I need to merge 2 more cells and i am unable to accomplish that. The "merge/center button" on the toolbar is not highlighted, so i am unable to merge the needed cells. Any suggestions? Any chance your worksheet is protected? brownk wrote: > > I am using an existing worksheet in Excel. Some cells have already been > merged. I need to merge 2 more cells and i am unable to accomplish that. > The "merge/center button" on the toolbar is not highlighted, so i am unable > to m...

formula field: don't display anything when value is zero (using ;"
hello, I read this under the topic "How to get a formula field to total an entire table column, even if some cells in the column contain text or are blank" on the MVP-site and can't get it to work. I'm interested in the final bit, using ;"" Does anyone know how to do this? <start quotation>... Then in the total cell, press Ctrl+F9, and within the field braces {}, insert the following formula: { SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" } “Table1” in the formula refers to the name of the bookmark you've marked the ...

Sometimes can see all text in a cell, sometimes not.
Hello On Sheet1, I can see all text in cells that have lots of text in them, but have cells to the right that are empty. On Sheet 2, I pasted the following formula into cell A1, and copied this into all of the cells on Sheet2: =IF(Sheet1!A1=0,"",Sheet1!A1) Now I can't see all of the text in a cell, even if the cell to the right of it is empty. Is there anything that can be done? I am copying these cells and pasting them into Word as a picture. Thanks! Its because if you type text into a cell it will spill over into empty cells so it can be read. If you then enter in a...

Outlook 2003 locks up while adding address to new mail message
I have Outlook 2003 installed with Business Contact Manager and Word as my email editor. Frequently, Outlook will lock up while I'm in the process of creating a new message and adding my first address in the To: field. I suspect that it has something to do with the AutoComplete feature as it appears to occur when the name is not automatically recognized or displayed even though it has been previously. Has anyone else encountered this bug and/or found a solution? I have the latest Outlook SPs installed. Haven't seen it, but it is more likely a corrupted profile than a corrup...

Cell sizing
Hi everyone, I'm recently new to all of this so I hope this isn't a stupid question. Before using Excel, about 7 or 8 years ago, I used Corel Quattro pro. I was able to set the cell heights and widths in mm, cm , inches, points, etc., so that when I printed a section of a sheet it would be a specific dimension. I haven't been able to do this with Excel. Thanks for your help. Only MacXL 2004 allows you to set height and width directly in inches/cm/mm. Both platforms allow you to set row height in points. Column widths can be set in the number of widths of the zero character in...

New Event Appears on Calendar
New events are appearing on my Outlook calendar that I have not created. They are blank execpt for the name "New Event" and the Start and End time are filled in. I did not create these events and don't know why they appear randomly on my calendar. I am using Outlook 2007, and the company uses Exchange Server 2007 SP2. -- Carolyn Do you use any addins? Sync with a device? Use a Dell laptop? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: ma...

New to Outlook #9
Some E-mail gives me an error stating error with previewer. MS Office Outlook message and item previewer. How do I correct this problem, and be able to view my mail? Danny <Danny@discussions.microsoft.com> wrote: > Some E-mail gives me an error stating error with previewer. MS Office > Outlook message and item previewer. How do I correct this problem, > and be able to view my mail? Using Outlook 2007? Always state your Outlook version. What distinguishes the mail that does not give you the error from that which does? Do you have attachments? If so, what type of documen...

Pop Up comment not popping up
All of the sudden my pop up comments stopped working. In fact my macros have errors and everything is wigging out. Does anyone have any suggestions on what to try? Thanks, Julie ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** ...

The summary bar is not displaying progress, once a milestone is co
I'm updating my milestone programme, but the progress line is not displaying as I require. A past milestone has been marked as complete, however the progress line is snagged on the left hand side of the summary bar above. I want the progress line to not snag of the summary bar, as this indicates the overall programme is in delay which it is not, please help. This is a multi-part message in MIME format. ------=_NextPart_000_0067_01CAB15B.08187000 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi Carlo, Wel...

Conditional text in group footer
I am new to Access and am trying to figure out how to set a text control in a group footer to "A", then change it to "B" if a condition is met somewhere in the details of the group. The data would look like: Group1, Group2, Group3, "A" Group1, Group2, Group3, "B" Group1, Group2, Group3a, "A" Group1, Group2, Group3a, "C" In the footer of Group3 I have a text control, when I hit the detail record = "B" I set the control to "B". Where I have trouble is where do I reset the text control back to "A" at the ...