Office: 2x Worksheet_Change(ByVal Target As Range)

Hallo zusammen,

ich bin VBA-Anfänger und habe die ersten Codes erstellt. Nun möchte ich 2 von Ihnen, welche einzeln funktionieren, zusammenfügen, so dass beide entsprechend durchgeführt werden.

Kopiere ich diese aneinander, wird mir angezeigt, dass der Name doppelt vergeben wurde. Leider verstehe ich noch nicht, wie der Name definiert wird und wie ich die Kombination herstellen kann. Über Hilfe wäre ich dankbar :).

Code1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I7:I9999")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub 'Bearbeiten mehrerer Zeilen wird abgefangen
If Target = "Erledigt" Then
Target.Offset(0, 10) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
Else:
Target.Offset(0, 10).ClearContents
End If
End Sub

Code2:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("N7:N9999")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub 'Bearbeiten mehrerer Zeilen wird abgefangen
If Target = "Sofort" Then
Target.Offset(0, 3) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
Else:
Target.Offset(0, 3).ClearContents
End If
End Sub

 

lösche diese Zeilen
"
End Sub

Code2:"
es darf nur 1 Change geben.

 

Hallo Hajo,

danke für die schnelle Antwort. Nun wird mir zwar keine Fehlermeldung mehr angezeigt und der 1. Code funktioniert, der 2. leider nicht. Wenn ich den 1. Code lösche, funktioniert der 2. Aktuell sieht es wie folgt aus:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I7:I9999")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub 'Bearbeiten mehrerer Zeilen wird abgefangen
If Target = "Erledigt" Then
Target.Offset(0, 10) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
Else:
Target.Offset(0, 10).ClearContents
End If
If Intersect(Target, Range("N7:N9999")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub 'Bearbeiten mehrerer Zeilen wird abgefangen
If Target = "Sofort" Then
Target.Offset(0, 3) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
Else:
Target.Offset(0, 3).ClearContents
End If
End Sub

 

Hi,

der Name ist das, was nach Private Sub und vor der ersten Klammer steht. Und hier gilt: "es kann nur Einen geben". Sprich du kannst Worksheet_Change nur ein einziges mal verwenden. Es nützt auch nichts eine der beiden Routinen z.B. Worksheet_Change2 oder so zu nennen. Denn die wird nicht ausgeführt (zumindest nicht automatisch). Du musst also alles in eine Sub packen.

Im einfachsten Fall löschst du das erste End Sub und das zweite Private Sub Worksheet_Change(ByVal Target As Range). Das reicht schon, wenn die Routinen vernünftig geschrieben wurden. Das ist hier leider nicht der Fall. Wieso? Weil einige Bedingungen geprüft werden, die, wenn sie zutreffen, die Routine mit Exit Sub abbrechen. Das darf nicht sein, weil sonst der zweite Teil der Routine auch nicht mehr durchlaufen wird.

Was ist zu tun? Dreh die Bedingungen um und arbeite nur dann die Routine ab statt sie zu beenden. Also aus

Code:

If Intersect(Target, Range("I7:I9999")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
...
If Intersect(Target, Range("N7:N9999")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
...

wird

Code:

If Not Intersect(Target, Range("I7:I9999")) Is Nothing Then
    If Target.Count = 1 Then
        ...
    End If
End If
If Not Intersect(Target, Range("N7:N9999")) Is Nothing Then
    If Target.Count = 1 Then
        ...
    End If
End If

Das gilt natürlich auch für alle anderen If xxx Then Exit Sub. Ich hoffe das Prinzip ist klar geworden.

PS: Verwende besser Target.CountLarge statt Target.Count. Wieso? Markiere mal die komplette Tabelle (indem du z.B. Strg-A drückst) und drücke dann die Entf-Taste. Dann merkst du wieso.

 

Ist doch klar, wenn Du mit Exit Sub arbeitest.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("I7:I9999")) Is Nothing Then
        If Target.Count < 1 Then
            If Target = "Erledigt" Then
                Target.Offset(0, 10) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
            Else
                Target.Offset(0, 10).ClearContents
            End If
        End If
    ElseIf Not Intersect(Target, Range("N7:N9999")) Is Nothing Then
        If Target.Count < 1 Then
            If Target = "Sofort" Then
                Target.Offset(0, 3) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
            Else
                Target.Offset(0, 3).ClearContents
            End If
        End If
    End If
End Sub

Ich baue keine Datei nach. Die Zeit hat schon jemand investiert.

Ein Nachbau sieht meist anders aus als das Original. <B>Darum sollte das Original verlinkt werden.</b>

Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten
Hinweis - Anonymisierung und Pseudonymisierung von Beispielmappen - MS-Office-Forum
änderrn.
Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Muster*, Test*, Mappe*, Beispiel*, Fehler*, Kalender* usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Es sollte ein aussagekräftiger Name sein.

Benutze hier im Forum die Funktion zum hochladen. Falls Du die nicht benutzen möchtest beachte, von unsicheren Servern wie z.B. www.file-upload.net lade ich keine Datei runter. (lt. Einschätzung meines Virenprogramms)3

Bilder lade ich mir nicht runter, da Excel damit nichts anfangen kann.

Bilder statt Datei | Excel ist sexy!

Hochgeladene Bilder können zwar als solche in Excel importiert werden, sind jedoch bei der Lösung von Problemen nicht sehr hilfreich, da man die eigentlichen Daten nicht ohne große und zeitraubende Umwege direkt in die Tabelle übertragen kann.

Das ist nur meine Meinung zu dem Thema.

Gruß Hajo

 

Hi,

du kannst beide Bedingungen in Form einer If.....ElseIf.....End Ef - Verzweigung mit einander verbinden:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub 'Bearbeiten mehrerer Zeilen wird abgefangen
    ' nur im Zellbereich I7:I9999 ausführen
    If Not Intersect(Target, Range("I7:I9999")) Is Nothing Then
        If Target = "Erledigt" Then
            Target.Offset(0, 10) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
        Else:
            Target.Offset(0, 10).ClearContents
        End If
    ' nur im Zellbereich N7:N9999 ausführen
    ElseIf Not Intersect(Target, Range("N7:N9999")) Is Nothing Then
        If Target = "Sofort" Then
            Target.Offset(0, 3) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
        Else:
            Target.Offset(0, 3).ClearContents
        End If
    End If
End Sub

---

 

Vielen Dank für eure Zeit und die ausführlichen Antworten, mit einer Kombination aus allem hat es nun geklappt :)

Unten noch eine weitere Frage. Aktuell funktioniert es wie folgt:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I7:I9999")) Is Nothing Then
If Target.Count = 1 Then
If Target = "Erledigt" Then
Target.Offset(0, 10) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
Else
Target.Offset(0, 10).ClearContents
End If
End If
End If
If Not Intersect(Target, Range("N7:N9999")) Is Nothing Then
If Target.Count = 1 Then
If Target = "Sofort" Then
Target.Offset(0, 3) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))
Else
Target.Offset(0, 3).ClearContents
End If
End If
End If
End Sub

Die beiden Range Targets sind Spalten mit Namen in einer formatierten Tabelle. Kann ich auch mit mit diesen Namen für Target und Target.Offset arbeiten?

 

Hi,

es gibt keine "Range Targets" sondern höchstens eine Range-Variable mit Namen Target.

Ich vermute du meinst die beiden Ranges, bei denen ermittelt wird, ob sie Target (also die geänderten Zellen) enthalten. Nehmen wir an, deine Strg-T-Tabelle heißt "MeineTabelle" und Spalte I hat die Überschrift "Status" und N heißt "Bis". Dann lauten die Code-Zeilen

Code:

If Not Intersect(Target, Me.ListObject("MeineTabelle").ListColumns("Status")) Is Nothing Then
...
If Not Intersect(Target, Me.ListObject("MeineTabelle").ListColumns("Bis")) Is Nothing Then

Damit hast du dann deinen Bereich auf die entsprechenden Spalten deiner Tabelle festgelegt.

Wenn du die Offset-Zahlen durch Spalten ersetzen willst und Spalte S (10 Spalten rechts von Spalte I) "Datum Status" heißt, dann wird aus

Code:

Target.Offset(0, 10) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))

z.B. die Zeile

Code:

Cells(Target.Row, Me.ListObject("MeineTabelle").ListColumns("Datum Status").Column) = CDate(Format(Now, "dd.mm.yyyy hh:mm:ss"))

Also in meinen Augen ist die Offset-Variante einfacher.

EDIT:
Übrigens ist CDate(Format(Now, "dd.mm.yyyy hh:mm:ss")) genau identisch zu Now. Denn Now liefert schon einen Date-Wert zurück. Mit Format(xxx) machst du daraus einen String und mit CDate(xxx) wieder einen Date-Wert.

 

Hallo HKindler,

danke für deine ausführliche Antwort samt Erklärung. Soweit habe ich den Sinn der Sache verstanden. Die Vereinfachung bzgl. der Datumsausgabe konnte ich umsetzen. Mit der Änderung der Spaltennamen hat es leider noch nicht geklappt. Vielleicht kannst du mir da noch zum Ziel helfen :)

Folgende Namen benutze ich aktuell:
Datei: Aufgabenliste_PT
Reiter: Aufgaben
Tabelle: Aufgabenliste
Spalte: DDL_Status

So wie ich dich verstanden habe, spielt der Name der Datei und des Reiters keine Rolle. Daher habe ich den Namen der Tabelle verwendet. Dann bekomme ich eine Fehlermeldung "Methode oder Datenobjekt nicht gefunden", wobei "Me.ListObject" markiert wird. Die "Aufgabenliste" ist im Namen-Manager mit Bereich Arbeitsmappe zu finden.

 

Hi,

in dem Fall wäre Me.ListObjects("Aufgabenliste").ListColumns("DDL_Status") korrekt. Sorry, bei meinen Post habe ich irgendwie das "s" unterschlagen.

Die genannten Zeilen gehören ins Worksheet_Change-Ereignis. Dieses wiederum befindet sich Codebereich des entsprechenden Blattes. Nur dann wird es auch automatisch aufgerufen. Und da es sich im Codebereich des Blettes befindet, bezeichnet das Me in Me.xxx genau dieses Blatt. Wenn du die Codezeielen irgendwo anders benutzen willst (also in einem allgemeinen Modul), dann musst du statt Me.xxx so etwas wie Worksheets("Blattname").xxx oder ThisWorkbook.Worksheets("Blattname").xxx gar Workbooks("Dateiname.xlsm").Worksheets("Blattname").xxx verwenden.