Couleur de l'onglet commentaires

Comment changer la couleur de l'Onglet rouge des commentaires

Ci-dessous, une fonction qui peut faire ça (d'un contributeur connu du forum anglophone). Le(s) commentaire(s) doivent commencer par le nom d'utilisateur qu'Excel inscrit par défaut (en gras, suivi de :).

sub test_fncCreateCommentIndicator()
   fncCreateCommentIndicator vbBlue, "nomDeLUtilisateur"
 end sub
 Public Function fncCreateCommentIndicator(CommentIndicatorColor As Long, _
  CommentIndicatorName As String) As Boolean 
 'covers the comment indicators in the activeworkbook with a similar 
 'triangle of the specified color, based on the Application.UserName property  
'Stratos Malasiotis (microsoft.public.excel.programming)  
 Dim IDnumber As Long  
 Dim aCell As Range 
  Dim aComment As Comment 
  Dim aShape As Shape  
 Dim aWorksheet As Worksheet  
 Dim aWorkbook As Workbook  

 fncCreateCommentIndicator = False  'check whether a code name has been entered  
 If CommentIndicatorName = vbNullString Then GoTo ExitFunction   
On Error GoTo ExitFunction  
 Set aWorkbook = ActiveWorkbook  
 IDnumber = 0  'loop through all wprksheets in the active workbook and all comments 
 'in each worksheet and create the comment shapes  
 For Each aWorksheet In aWorkbook.Worksheets     
For Each aShape In aWorksheet.Shapes      
 If Left(aShape.Name, Len(CommentIndicatorName)) = _          
 CommentIndicatorName Then      
  aShape.Delete       
End If    
 Next aShape     
For Each aComment In aWorksheet.Comments      
 Set aCell = aComment.Parent       
If InStr(1, aComment.Shape.TextFrame.Characters.Text, ":") > 0 Then        
 If Left(aComment.Shape.TextFrame.Characters.Text, InStr(1, aComment.Shape.TextFrame.Characters.Text, ":") - 1) = Application.UserName Then         
 Gosub CreateCommentIndicator
        End If
      End If
    Next aComment
  Next aWorksheet
  fncCreateCommentIndicator = True
 ExitFunction:
  On Error GoTo 0
  Set aCell = Nothing
  Set aComment = Nothing
  Set aShape = Nothing
  Set aWorksheet = Nothing
  Set aWorkbook = Nothing
  Exit Function
 CreateCommentIndicator:
  Set aShape = aWorksheet.Shapes.AddShape(Type:=msoShapeRightTriangle, _
      Left:=aCell.Left + aCell.Width - 5, _
      Top:=aCell.Top, _
      Width:=5, _
      Height:=5)
      IDnumber = IDnumber + 1
  With aShape
   .Name = CommentIndicatorName & CStr(IDnumber)
   .IncrementRotation -180#
   .Fill.Visible = msoTrue
   .Fill.Solid
   .Fill.ForeColor.RGB = CommentIndicatorColor
   .Line.Visible = msoTrue
   .Line.Weight = 1
   .Line.Style = msoLineSingle
   .Line.DashStyle = msoLineSolid
   .Line.ForeColor.RGB = CommentIndicatorColor
   .Placement = xlMove
  End With
  Return
 End Function

As it is now it will colour only the comments that keep the default name
given by excel (Application.username)

Auteurs : ,

Mots clefs associés à cette page : ,