Excel Cell Comments

[kirkm]

If I run the cursor over a commented cell, the Status bar
at the bottom of the screen says 'Cell xx commented by' and my name

Can I remove my name ?

Thanks - Kirk

 

[Gary''s Student]

View > and uncheck Status Bar

 

[Peter T]

Depending on overall needs maybe replace 'my name' with 'new name' in
comments.

See Debra Dalgleish's routine and more about comments here -
http://www.contextures.com/xlcomments03.html#OldName

I notice the original bold format of the username is lost but the code could
be adapted to re-embolden the username if required.

Also in the example, when done the username in tools/option can be reset to
original with username in the status bar continuing to display the new name
as replaced in the comments, if required.

Regards
Peter T

 

[kirkm]

http://www.contextures.com/xlcomments03.html#OldName

Thanks Peter. I had a play with the routine but it also reset format
and size of the (carefully setup) comments. Possibly I could copy
the existing attributes to the new comment, but I'm undecided if its
worth the effort. It was an interesting list of code examples, thanks.

Cheers - Kirk

 

[Peter T]

Indeed if you have mixed formats it could be quite a lot of work to store
and replace them. However merely to make UserName: bold, as per default,
wouldn't take too much.

If you look into that, perhaps change the comments loop to something like
this -

For Each cmt In ws.Comments
strCommentOld = cmt.Text
strCommentNew = Replace(strCommentOld, strOld, strNew)
If strCommentOld <> strCommentNew Then
Set rng = cmt.Parent
cmt.Delete
With rng.AddComment(Text:=strCommentNew)
.Shape.TextFrame.Characters(1, Len(strNew)).Font.Bold = True
End With
End If

Regards,
Peter T

 

[kirkm]

Indeed if you have mixed formats it could be quite a lot of work to store
and replace them. However merely to make UserName: bold, as per default,
wouldn't take too much.

If you look into that, perhaps change the comments loop to something like
this -

For Each cmt In ws.Comments
strCommentOld = cmt.Text
strCommentNew = Replace(strCommentOld, strOld, strNew)
If strCommentOld <> strCommentNew Then
Set rng = cmt.Parent
cmt.Delete
With rng.AddComment(Text:=strCommentNew)
.Shape.TextFrame.Characters(1, Len(strNew)).Font.Bold = True
End With
End If

I'm still experimenting, thanks for the ideas.

Have found if you add to Private Sub Workbook_Open()

Application.StatusBar = "Micosoft Excel"

.... then the old 'Cell xx commented by ' part doesn't appear. Although
would something restore it later on ?

Thanks - Kirk

 

[Peter T]

I'm still experimenting, thanks for the ideas.

Have found if you add to Private Sub Workbook_Open()

Application.StatusBar = "Micosoft Excel"

... then the old 'Cell xx commented by ' part doesn't appear. Although
would something restore it later on ?

Thanks - Kirk

If it's only comments on a particular sheet you don't want indicated in the
status bar, try the following in the worksheet module (rt-click sheet tab -
view code)

Private Sub Worksheet_Activate()
Application.StatusBar = "Ready and willing"
End Sub

Private Sub Worksheet_Deactivate()
Application.StatusBar = False
End Sub


Or similar to handle all sheets, try the following in the Thisworkbook
module (rt click XL icon left of 'File' menu and view-code)

' either
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.StatusBar = Sh.Name & " looking useful"
End Sub

' or
Private Sub Workbook_Activate()
'Application.StatusBar = Me.FullName
End Sub

' but not both the above

Private Sub Workbook_Deactivate()
Application.StatusBar = False
End Sub


Regards,
Peter T