Thema: Memory in Zellkommentar

ClassModule: DieseArbeitsmappe

Private Sub Workbook_Open()
   Application.DisplayCommentIndicator = xlCommentIndicatorOnly
   Range("A2").Select
   Range("A1").Select
End Sub

ClassModule: Tabelle1

Public var As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Value <> var Then
      If Not ActiveCell.Comment Is Nothing Then
         ActiveCell.Comment.Text ActiveCell.Comment.Text & vbLf & var
      Else
         ActiveCell.AddComment CStr(var)
      End If
      If Not IsEmpty(Target) Then
         Target.Interior.ColorIndex = 6
      Else
         Target.Interior.ColorIndex = 4
      End If
   End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Selection.Cells.Count > 1 Then Exit Sub
   var = Target.Value
End Sub

Sub ReSetComments()
   Dim cmt As Comment
   For Each cmt In ActiveSheet.Comments
      cmt.Delete
   Next cmt
End Sub

Beiträge aus dem Excel-Forum zu den Themen Ereignis und Change