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
|
|
|
|
|
Excel-Ereignisse im Überblick |
|
|
|
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.
|
|
|
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.
|
|