Applying a name change to existing comments in an Excel 2007 spreadsheet

novicee · May 8, 2014

Applying a name change to existing comments in an Excel 2007 spreadsheet

I have inserted comments into cells of an Excel 2007 spreadsheet using "right click mouse > insert comment ". When I hover the cursor over a cell with a comment, a message appears in the "task bar" (not sure this is the correct name for it) at the bottom of the page. The message says, for example, "Cell E7 commented by JD". I want to expand the initials "JD" to a name and apply the change to all the comments I have already entered.

I selected the options, "round MS button at top left of page > Excel Options > Popular > User Name ". The last option is under the heading " Personalize your copy of Microsoft Office". I changed "JD" to "John". This has become the name that new comments are attributed to but the old comments are still attributed to "JD".

Is there a way I can apply the name change to comments entered before the change was made?

I would be grateful for any help on this.

Fazza · May 9, 2014

welcome

Maybe VBA is best for this work. I guess google will find examples, but I wrote something anyway. I welcome the practice.

From an Excel worksheet, ALT-F11 to get into the VB editor, copy & paste from below into the big white window on the RHS. Position the cursor within the middle of the pasted code and hit F5

Go back to the worksheet and see if it worked. if so, back to the editor & delete the code

HTH. Regards, Fazza

Code:

Sub maybe()

Const str_CHANGE_FROM = "JD"
Const str_CHANGE_TO = "John"


Dim rng As Range
Dim wks As Worksheet


For Each wks In Worksheets
  For Each rng In wks.Cells.SpecialCells(xlCellTypeComments)
    rng.Comment.Text Replace$(rng.Comment.Text, str_CHANGE_FROM, str_CHANGE_TO)
  Next rng
Next wks
Set wks = Nothing


End Sub

novicee · May 9, 2014

Fazza, thanks very much. I will try your code and report back on whether it works.

Fazza · May 12, 2014

There is plenty of code for working with comments at Debra Dalgleish's great site : http://www.contextures.com/xlcomments03.html

regards

Applying a name change to existing comments in an Excel 2007 spreadsheet

novicee

New Member

Excel Facts

Fazza

MrExcel MVP

novicee

New Member

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

Applying a name change to existing comments in an Excel 2007 spreadsheet

novicee

New Member

Excel Facts

Fazza

MrExcel MVP

novicee

New Member

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock