Option Explicit
Dim m As String 'adresse de la cellule avec commentaire affiché
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call CentreCommnetaire1(Target)
End Sub
Public Sub IniCacheCommentaires() ' initialisastion des commentaires
Dim Cmnt As Comment
If ActiveSheet.Comments.Count = 0 Then Exit Sub 'absence de commentaire
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each Cmnt In ActiveSheet.Comments 'boucle sur tous les commentaires
With Cmnt.Shape
.TextFrame.Characters.Font.ColorIndex = 2 'met le texte en blanc
.Shadow.Visible = False 'supprime l'ombre
.Fill.Transparency = 1# 'fond 100% de transparent
.Line.Transparency = 1# 'cadre 100% de transparent
End With
Cmnt.Visible = False 'commentaire masqué
Next Cmnt
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub CentreCommnetaire1(ByVal Target As Range)
Dim HautImage As Long, GaucheImage As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
If m <> "" Then 's'il y a un commentaire affiché, on le cache
With Range(m).Comment.Shape
.TextFrame.Characters.Font.ColorIndex = 2 'met le texte en blanc
.Shadow.Visible = False 'masque l'ombre
.Fill.Transparency = 1# 'fond 100% de transparent
.Line.Transparency = 1# 'cadre 100% de transparent
End With
Range(m).Comment.Visible = False ' commentaire masqué
End If
If Not Target.Comment Is Nothing Then 'si Target a un commentaire, on l'affiche
With Target.Comment.Shape
.TextFrame.Characters.Font.ColorIndex = 1 'met le texte en noir
.Shadow.Visible = True 'affiche l'ombre
.Fill.Transparency = 0# 'fond 0% de transparent
.Line.Transparency = 0# 'cadre 0% de transparent
End With
Target.Comment.Visible = True 'affichage du commentaire
'centrage du commentaire
With ActiveWindow.ActivePane.VisibleRange
HautImage = .Top + (.Height / 2)
GaucheImage = .Left + (.Width / 2)
End With
With Target.Comment.Shape
.Top = HautImage - (.Height / 2)
.Left = GaucheImage - (.Width / 2)
End With
m = Target.Address 'mémorisation si commentaire affiché
Else
m = "" 'pas de commentaire affiché
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub