?
Solved

How to change the comments name in excel

Posted on 2007-07-22
10
Medium Priority
?
274 Views
Last Modified: 2010-03-05
Hi,

I have a shared excel.When ever a user inserts a comment.He gets his name into the comment.Is there a way to remove the names and even change the names to the desired name.

Regards
Sharath
0
Comment
Question by:bsharath
10 Comments
 
LVL 26

Expert Comment

by:Farhan Kazi
ID: 19545248
This username is picked from following location:
Tools -> Options -> General Tab -> in 'User name' Textbox
0
 
LVL 11

Author Comment

by:bsharath
ID: 19545259
Already there are 100's of comments .Need to change all the comments.
Remove the name alone from the box or Change the name to new name..
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 19545270
Options>General>User name determines what name is used in the comment. You can edit or remove the name in comments manually as well, insert>edit comment

BR,
Curt
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 19545368
BSHARATH

The following macro sort of does it BUT all the text is left as bold, haven't figured out how to resolve it.  May be enough for your purposes though.

Sub ee_22713365()
Dim cmt As Comment
Dim cmt_txt As String
Dim target_name As String
Dim end_name As Long
target_name = "BSHARATH"


For Each cmt In ActiveSheet.Comments
    cmt_txt = cmt.Text
    end_name = InStr(cmt_txt, ":")
    If LCase(Trim(Left(cmt_txt, end_name - 1))) <> LCase(target_name) Then
        cmt.Text Text:=target_name & Right(cmt_txt, Len(cmt_txt) - end_name + 1)
    End If
Next
End Sub

Regards
Chris
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 19545400
This macro should remove the usernames

Sub RemoveUserNameFormComment()
    Dim c As Comment
    Dim sht As Worksheet
    On Error Resume Next
       For Each sht In ActiveWorkbook.Sheets
            For Each c In sht.Comments
                commtext = c.Text
                intRow = Application.Find(Chr(10), commtext, 1)
                If intRow = 0 Then
                Else
                commtext = Mid(commtext, intRow + 1, 12)
                c.Text Text:=commtext
                End If
            Next c
        Next sht
End Sub

BR,
Curt
0
 
LVL 11

Author Comment

by:bsharath
ID: 19545426
chris_bottomley

It is making everything bold but not removeing anything.

epaclm
this is clearing the name.Any way to change the name to another name?
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 19545436
The macro RemoveUserName comes from the following question which has several working macros which may be useful depending on what you really want to achieve.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21048690.html

BR,
Curt
0
 
LVL 11

Author Comment

by:bsharath
ID: 19545448
The link what you have given does not change the names but it is a accepted answer.
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 19545455
You could for example use the last macro in this question first and replace "New user" with whatever you like. This will show the name you want in the statusbar. Then you could run the RemoveUserName macro to clear the original names.

This is the last macro I'm talking about:

Sub cAuthor()
Dim tSh As Worksheet, dSh As Worksheet, i As Integer
Dim c As Comment, sAuthor As String, d As Comment
sAuthor = Application.UserName: Application.UserName = "NewUser"
Set tSh = ActiveSheet: tSh.Copy Before:=Sheets(1)
Set dSh = Sheets(1)
For Each c In tSh.Comments
c.Delete
Next c
For Each c In dSh.Comments
tSh.Range(c.Parent.Address).AddComment c.Text
Set d = tSh.Range(c.Parent.Address).Comment
With d.Shape
.Left = c.Shape.Left
.Top = c.Shape.Top
.Width = c.Shape.Width
.Height = c.Shape.Height
End With
For i = 1 To d.Shape.TextFrame.Characters.Count
With d.Shape.TextFrame.Characters(i, 1).Font
On Error Resume Next
.Name = c.Shape.TextFrame.Characters(i, 1).Font.Name
.Bold = c.Shape.TextFrame.Characters(i, 1).Font.Bold
.Size = c.Shape.TextFrame.Characters(i, 1).Font.Size
.ColorIndex = c.Shape.TextFrame.Characters(i, 1).Font.ColorIndex
On Error GoTo 0
End With
Next i
Next c
Application.DisplayAlerts = False
dSh.Delete: Set dSh = Nothing: Set tSh = Nothing
Application.DisplayAlerts = True
Application.UserName = sAuthor
End Sub

BR,
Curt
0
 
LVL 18

Accepted Solution

by:
Curt Lindstrom earned 2000 total points
ID: 19545483
To replace the name in the comment try this: change "John Smith" to whatever you like.

Sub RemoveUserNameFormComment()
    Dim c As Comment
    Dim sht As Worksheet
    On Error Resume Next
       For Each sht In ActiveWorkbook.Sheets
            For Each c In sht.Comments
                commtext = c.Text
                intRow = Application.Find(Chr(10), commtext, 1)
                If intRow = 0 Then
                Else
                commtext = Mid(commtext, intRow + 1, 12)
                c.Text Text:="John Smith: " & commtext
                End If
            Next c
        Next sht
End Sub
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question