Tipp 0140 Excel-Ereignisse des Worksheet-Objekts
Autor/Einsender:
Datum:
  Angie
22.10.2006 (Update)
Entwicklungsumgebung:   VB 6
Bei der Automatisierung von Excel können bestimmte Ereignisse, die ggf. durch den Anwender ausgelöst werden, überwacht werden. Die Verarbeitung von Ereignissen von Blättern und Arbeitmappen ist relativ einfach, da bereits im Objekt-Modell von Excel über 50 Ereignisse definiert sind, manche davon sind allerdings mehrfach definiert. Siehe hierzu auch unseren Tipp Excel-Ereignisse des Workbook-Objekts.
Sehr oft verwendete Ereignisse des Worksheet-Objekts (Tabellenblatt) sind die Change- und SelectionChange-Ereignisse. Um auf die Aktionen des Anwenders im Tabellenblatt reagieren zu können, können entweder die Ereignisse des Application- oder Workbook-Objekts verwendet werden, oder aber wie in diesem Beispiel, die Ereignisse des Worksheet-Objekts, die auch bei der Automatisierung von Excel von Visual Basic aus verwendet werden können.
Code im Klassenmodul CWksEvents
Im Klassenmodul CWksEvents werden alle Objekt-spezifischen Eigenschaften, Methoden und Ereignisse untergebracht, hier die des Tabellenblatts (Worksheet). Diese Klasse dient als Vorlage, aus der eine Objektinstanz zur Laufzeit erzeugt wird. Es wird für jedes Tabellenblatt eine Objektinstanz erstellt.
Um auf die entsprechenden Ereignisse reagieren zu können, wird bei der Definition der Objektvariable für das Tabellenblatt das Schlüsselwort WithEvents benutzt.
Folgendes Beispiel beinhaltet zwei Worksheet-Ereignisse, das Change-Ereignis für Tabellenblätter, das eintritt, nachdem eine Zelle in einem Tabellenblatt verändert oder gelöscht wurde und das SelectionChange-Ereignis, das auftritt, wenn der Anwender die Auswahl der markierten Zellen verändert.
Für die Anzeige der Meldungen in Excel wurde in diesem Beispiel ein Zeichnungsobjekt (Shape) statt einer MsgBox verwendet.
 
Option Explicit

Private WithEvents mevtXLWks As Excel.Worksheet
Private mobjXLShape As Excel.Shape

Private Sub Class_Terminate()
  Set mobjXLShape = Nothing
  Set mevtXLWks = Nothing
End Sub

Public Property Set Worksheet(ByRef objXLWks As Excel.Worksheet)
  Set mevtXLWks = objXLWks
End Property

Public Property Get Worksheet() As Excel.Worksheet
  Set Worksheet = mevtXLWks
End Property

Private Sub mevtXLWks_SelectionChange(ByVal Target As Excel.Range)
  Dim strMsg As String
  strMsg = "Excel-Ereignisse des Worksheet-Objekts" & vbLf & vbLf
  strMsg = strMsg & "Im Tabellenblatt '" & mevtXLWks.Name
  strMsg = strMsg & "' wurde der Bereich '" & Target.Address
  strMsg = strMsg & "' ausgewählt."

  Call DisplayXLShape(300, 110, strMsg)
End Sub

Private Sub mevtXLWks_Change(ByVal Target As Excel.Range)
  Dim objXLCell As Excel.Range
  mevtXLWks.Application.EnableEvents = False
  For Each objXLCell In Target
    If IsNumeric(objXLCell.Value) Then
      objXLCell.Interior.ColorIndex = 36
    Else
      objXLCell.Value = ""
      objXLCell.Interior.ColorIndex = xlColorIndexNone
    End If
  Next
  mevtXLWks.Application.EnableEvents = True
End Sub

Private Sub DisplayXLShape(ByVal sngWdth As Single, _
      ByVal sngHght As Single, ByVal strMsg As String)

  Const msoShapeRoundedRectangle As Long = 5
  Dim i As Integer

  On Error Resume Next
  mevtXLWks.Application.ScreenUpdating = False
  mobjXLShape.Delete
  Set mobjXLShape = mevtXLWks.Shapes.AddShape _
        (msoShapeRoundedRectangle, 50, 50, sngWdth, sngHght)

  With mobjXLShape
    .Line.Weight = 1.5
    .Line.ForeColor.SchemeColor = 64
    .Line.BackColor.RGB = RGB(255, 255, 255)
    .Placement = xlFreeFloating
  End With

  With mobjXLShape.TextFrame
    .Characters.Text = ""
    For i = 0 To Int(Len(strMsg) / 255)
      .Characters(.Characters.Count + 1).Insert _
          Mid(strMsg, (i * 255) + 1, 255)
    Next

    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlVAlignCenter
    .AutoSize = False
  End With

  With mobjXLShape.TextFrame.Characters.Font
    .Name = "Arial"
    .FontStyle = "Standard"
    .Size = 10
    .ColorIndex = xlAutomatic
  End With

  mevtXLWks.Application.ScreenUpdating = True
  On Error GoTo 0
End Sub
 
Code im Klassenmodul col_Workbooks
In der Auflistungsklasse col_Workbooks (Collection) befinden sich die benötigten Methoden mit der der Auflistung neue Objekte hinzugefügt (Add) und gelöscht (Remove) werden können, ein bestimmtes Objekt (Item) und die Anzahl (Count) der in der Auflistung enthaltenen Objekte zurückgegeben werden kann. Die Objektdaten werden in einem Collection-Objekt gespeichert.
 
Option Explicit

Private mcolXLWkss  As Collection   'Collection-Object

Private Sub Class_Initialize()
  Set mcolXLWkss = New Collection
End Sub

Private Sub Class_Terminate()
  Set mcolXLWkss = Nothing
End Sub

Public Function Add(ByRef objXLWks As Excel.Worksheet) _
      As CWksEvents
  Dim objCWks As CWksEvents

  On Error GoTo err_Add
  Set objCWks = New CWksEvents
  Set objCWks.Worksheet = objXLWks
  mcolXLWkss.Add objCWks
  Set Add = objCWks
  Set objCWks = Nothing

exit_Func:
  On Error GoTo 0
  Exit Function

err_Add:
  MsgBox "Fehler: " & Err.Number & vbCrLf & _
        Err.Description, vbOKOnly + vbCritical
  Resume exit_Func
End Function

Public Property Get Item(ByVal Index As Variant) As CWksEvents
  Set Item = mcolXLWkss.Item(Index)
End Property

Public Property Get Count() As Long
  Count = mcolXLWkss.Count
End Property

Public Sub Remove(ByVal Index As Variant)
  mcolXLWkss.Remove Index
End Sub
 
Code im Codebereich der Form
Im Codebereich der Form befindet sich unter anderem der Code zum Starten und Beenden der Excel-Anwendung als auch für das Erstellen der neuen Arbeitsmappe, für dessen Tabellenblätter die Ereignisse definiert werden.
 
Option Explicit

Private mobjXLApp  As Excel.Application
Private mobjXLWkb  As Excel.Workbook
Private mcolXLWkss As col_Worksheets

Private Sub cmdStartXL_Click()
  Dim objXLWks As Excel.Worksheet

  On Error GoTo err_Excel
  Set mobjXLApp = CreateObject("Excel.Application")
  Set mcolXLWkss = New col_Worksheets

  Set mobjXLWkb = mobjXLApp.Workbooks.Add
  For Each objXLWks In mobjXLWkb.Worksheets
    mcolXLWkss.Add objXLWks
  Next

  mobjXLApp.Visible = True
  mobjXLApp.UserControl = True

exit_Sub:
  On Error Resume Next
  Me.cmdStartXL.Enabled = False
  Me.cmdQuit.Default = True

  On Error GoTo 0
  Exit Sub

err_Excel:
  MsgBox "Fehler bei der Verbindung zu Excel!", vbCritical
  Resume exit_Sub
End Sub

Private Sub cmdQuit_Click()
  Unload Me
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, _
      UnloadMode As Integer)
  On Error Resume Next
  Set mcolXLWkss = Nothing

  mobjXLWkb.Close False
  Set mobjXLWkb = Nothing

  mobjXLApp.Quit
  Set mobjXLApp = Nothing
  On Error GoTo 0
End Sub
 
Weitere Links zum Thema
Automation mit Office-Anwendungen
Excel-Automatisierungsfehler vermeiden
Excel-Ereignisse des Workbook-Objekts
Excel-Ereignisse im Überblick
Ereignisse des Application-Objekts
Ereignisse des Chart-Objekts
Ereignisse des Workbook-Objekts
Ereignisse des Worksheet-Objekts
Hinweis für VBA-Anwender
Im Download befindet sich auch ein Word/VBA-Beispiel. Wird Excel über eine UserForm gestartet, wie in diesem Beispiel, dann muss die UserForm ungebunden angezeigt werden, ansonsten "stürzt" Excel ab.
Hinweis
Um diesen Tipp ausführen zu können, muss Excel installiert sein und zur Entwurfszeit die Microsoft Excel x.0 Object Library in das Projekt eingebunden werden.
Für die Anzeige der Meldungen im Zeichnungsobjekt (Shape) in Excel ist bei Early Binding zusätzlich das Einbinden der Objektbibliothek Microsoft Office x.0 Object Library notwendig. Bei Late Binding wird zur Entwurfszeit kein Verweis auf die Objektbibliothek in das Projekt eingebunden. Alle verwendeten Objekte werden mit dem Variablentyp Object definiert. Ggf. verwendete anwendungsspezifische Konstanten müssen mit dem entsprechenden numerischen Äquivalent der Konstante ersetzt werden, könnten aber auch mit der Const-Anweisung deklariert werden. Die Verwendung von Konstanten erleichtert das Dokumentieren und Modifizieren Ihrer Programme, und trägt auch zur besseren Lesbarkeit des Codes bei.

Windows-Version
95
98/SE
ME
NT
2000
XP
Vista
Win 7
VB-Version
VBA 5
VBA 6
VB 4/16
VB 4/32
VB 5
VB 6


Download  (38 kB) Downloads bisher: [ 1649 ]

Vorheriger Tipp Zum Seitenanfang Nächster Tipp

Startseite | Projekte | Tutorials | API-Referenz | VB-/VBA-Tipps | Komponenten | Bücherecke | VB/VBA-Forum | VB.Net-Forum | DirectX-Forum | Foren-Archiv | DirectX | VB.Net-Tipps | Chat | Spielplatz | Links | Suchen | Stichwortverzeichnis | Feedback | Impressum

Seite empfehlen Bug-Report
Letzte Aktualisierung: Sonntag, 28. August 2011