Excel und VBA: Formeln, Programmierung, Lösungen

ClassModule: Tabelle21

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   Dim wks As Worksheet
   Dim oCmt As Comment
   Dim var As Variant
   Set wks = Worksheets("Kürzel")
   If Target.Column <> 1 Then Exit Sub
   If IsEmpty(Target) Then Exit Sub
   If Target.Cells.Count > 1 Then Exit Sub
   Application.DisplayCommentIndicator = xlCommentIndicatorOnly
   var = Application.Match(Target.Value, wks.Columns(1), 0)
   If Not IsError(var) Then
      Application.EnableEvents = False
      Target.Value = wks.Cells(var, 2).Value
      If Not Target.Comment Is Nothing Then
         Target.Comment.Delete
      End If
      Set oCmt = Target.AddComment(wks.Cells(var, 3).Value)
      oCmt.Shape.TextFrame.AutoSize = True
      On Error GoTo ERRORHANDLER
   End If
ERRORHANDLER:
   Application.EnableEvents = True
End Sub

    

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