Office: (Office 2010) Excel vba automatische Datumsabfrage beim öffnen

Hallo an alle die sich besser mit vba auskennen als ich (was nicht schwer ist)

Ich habe mir ein Projekt aufgehalst, welches meine vba Kenntnis in vielen Fällen überschreitet. Durch Hilfe bin ich schon weit gekommen, nur leider hänge ich jetzt wieder fest.

Zur Datei: Es ist im Grude eine Lagerliste von Spalte 1-20, in der Eingelagert, gesucht/gelöscht und Updates vollzogen werden können. Das heißt aber auch, das ich eine unbestimmte Zeilen Anzahl habe. Beginnen tut die Tabelle bei Zeile 6.
Ich hätte jetzt gerne das es bei öffnen der Datei, eine automatische Datumsabfrage vollzieht. Soll heißen, er soll alle Zeilen von Spalte 1 -20 farblich markieren, wo das Datum in Spalte 7 unter dem aktuellen Datum liegt. Ich weiß, dass dies auch über BedingteFormatierung möglich ist, hätte es aber lieber über vba.

Ich habe da schon war geschrieben, nur leider funktioniert es nicht. Ich weiß, das mit meiner Datumsabfrage etwas nicht stimmt, weiß aber nicht richtig wie ich ihm Sagen soll, der er immer das aktuelle Datum als Referenz nehmen soll. Vielleicht kann mir ja jemand helfen!

Tabellenblatt Abfrage:

Private Sub Workbook_open()

Call DatumAbfrage

End Sub

Modul zu Abfrage:

Option Explicit

Const C_mstrDatenblatt As String = "Verbrauchsmaterial"
Dim mlngLast As Long
Dim mlngz As Long

Private Sub DatumAbfrage()

mlngLast = Worksheets(C_mstrDatenblatt).Cells(Rows.Count, 1).End(xlUp).Row

With Worksheets(C_mstrDatenblatt)
For mlngz = 6 To mlngLast

If Cells(mlngz, 7) < datum Then
Range(Cells(mlngz, 1), Cells(mlngz, 20)).Interior.ColorIndex = 45
End If
Next mlngz

End Sub


Vielen Dank und einen lieben Gruß

Katja

 

Hallo Katja,

Im Codemodul von "DieseArbeitsmappe" (bzw. "ThisWorkbook")

Code:

Option Explicit

Private Sub Workbook_Open()

  Call DatumAbfrage

End Sub

In einem allgemeinen Codemodul

Code:

Option Explicit

Const C_mstrDatenblatt As String = "Verbrauchsmaterial"
Dim mlngLast As Long
Dim mlngz As Long

[COLOR="#0000FF"][B]Public[/B][/COLOR] Sub DatumAbfrage()
   
   [COLOR="#0000CD"][B]With Worksheets(C_mstrDatenblatt)[/B][/COLOR]
      mlngLast = [COLOR="#FF0000"][B].[/B][/COLOR]Cells(Rows.Count, 1).End(xlUp).Row
   
      For mlngz = 6 To mlngLast
      
         If .Cells(mlngz, 7) < [COLOR="#0000CD"][B]Date[/B][/COLOR] Then
            Range([COLOR="#FF0000"][B].[/B][/COLOR]Cells(mlngz, 1), [COLOR="#FF0000"][B].[/B][/COLOR]Cells(mlngz, 20)).Interior.ColorIndex = 45
         End If
      Next mlngz
   [COLOR="#0000CD"][B]End With[/B][/COLOR]

End Sub

Bitte auch den Punkt jeweils vor jedem "Cells(...)" beachten - also ".Cells(...)"!!

 

Hallo Anton

Meine Fähigkeiten in dieses Forum zu schreiben sind anscheinend auch nicht so gut, ich habe schon zweimal eine Antwort geschrieben nur leider ist die hier nie Sichtbar...wer weiß was ich da falsch machen!

Danke auf jeden fall für deine Antwort! Die Punkte sind mir tatsächlich total entgangen, das die Datum statt Date geschrieben hatte, hatte ich dann ja noch erkannt! Bei meinen Code hatte das auch nichts verändert. Dein Code Funtioniert soweit, nur leider werden jetzt nur leere Datum Zellen färbt und die Zellen mit einen abgelaufenen Datum leider nicht mit.
Müsste ich evtl. diese <Date präziser schreiben? Und wäre es möglich, mit Do While not isempty zu verhindern das leer Zeilen markiert werden?

Ich habs Probiert, nur leider bekomme ich da nur Fehler im Bezug auf dem Loop für die Do while schleife.

Vielen Dank

 

Hallo Katja,

1. Frage: Wo kommt in deinem Code eine Do-While-Schleife vor? Ich dachte, es handelte sich um eine For-Next-Schleife.
2. Du schreibst, dass du "nur Fehler" erhältst. Das ist leider für mich wenig hilfreich. Wichtig ist für mich, damit ich dir helfen kann, zu wissen,
   • in welcher Codezeile
   • welche Fehler (Fehlernummer bzw. Kurzbeschreibung/Bezeichnung des Fehlers)
   aufgetreten ist.
3. Da du im übermittelten Code den "With ... End With"-Block falsch behandelt hast (das "With ..." in der falschen Zeile, ohne "End With", dazwischen keinen "."-Bezug auf das "With"), ist meine Frage, ob du dies im Code berichtigt hast, ob du vor allen 4 vorhandenen "Cells(...)" den Punkt (also ".Cells(...)") eingefügt hast?

Ich habe dir in der angefügten Excel-Datei deine zusätzlichen VBA-Codes eingefügt (ausgehend vom letzten Excel-Datei-Posting).
Sobald du die Datei öffnest, wird automatisch die SUB "DatumAbfrage" gestartet, und im Arbeitsblatt "Verbrauchsmaterial" alle Zeilen (die ersten 20 Spalten ab Zeile 6) orange eingefärbt, die in der Spalte G (=Spalte 7) ein Datum enthalten, das vor dem heutigen Datum liegt. Ist in Spalte G eine Datumszelle leer, so bedeutet das ein fortlaufendes Datum 0, was ins Datumsformat umformatiert dem "00.01.1900" (=31.12.1899) entspricht. Folglich werden solche Zeilen auch orange eingefärbt.

 

Hallo Anton
Vielen Dank für deine schnelle Antwort!

Das mit der Do While Schleife, war nur eine Frage ob dies überhaupt möglich wäre. Da ich nicht möchte das die Zeilen, welche mit keinen Datum versehen sind eine Färbung bekommen. Aber da kann ich ja auch bei meiner UserForm mit der ich die Daten einfüge, einen Befehl vorgeben dann dürfte ich das hin gekommen.

Die Punkte hatte ich hinzugefügt und den With - End With Block dann auch richtig gesetzt. Also funktioniert der Befehl auch soweit, das einige meiner Zeilen nicht gefühlt waren, lag am falschen Datumsformat. Ich Programmieren in einer extra Datei, welche Baugleich zur original Datei ist um da nichts zu verstören, dadurch sind einige meiner Eingaben nicht immer auf den neusten Vorgabe Stand!

Aber vielen Dank für die Hilfe, ich schau mal ob ich es mit einen If-Befehl hingekommen. Wenn eine Vorgabe schreibe, dass das Datum dann automatsch auf 01.01.2200 gesetzt wird hat sich das Problem mit den leeren Zeilen ja erledigt.

LG Katja

 

Hallo Katja,

um eine Zeile ohne Datum in Spalte G nicht einzufärben, brauch man nur ein zusätzliches IF Not IsEmpty(...) Then ... End If einzufügen. Voraussetzung ist, dass diese Zelle tatsächlich gänzlich leer (unausgefüllt) ist, also auch nicht eine 0 oder "" enthält:

Code:

Option Explicit

Const C_mstrDatenblatt As String = "Verbrauchsmaterial"
Dim mlngLast As Long
Dim mlngz As Long

Public Sub DatumAbfrage()
   
   With Worksheets(C_mstrDatenblatt)
      mlngLast = .Cells(Rows.Count, 1).End(xlUp).Row
   
      For mlngz = 6 To mlngLast
[COLOR="#0000CD"]         If Not IsEmpty(.Cells(mlngz, 7)) Then
[/COLOR]            If .Cells(mlngz, 7) < Date Then
               Range(.Cells(mlngz, 1), .Cells(mlngz, 20)).Interior.ColorIndex = 45
            End If
[COLOR="#0000CD"]         End If
[/COLOR]      Next mlngz
   End With

End Sub

 

Hallo Anton

Vielen Dank, es funktioniert und auch die Leeren Zeilen bleiben ohne Farbe!
Auch wenn es bestimmt eine völlig einfache Lsg. ist, wäre ich darauf nie gekommen!

Einen schönen Tag noch und nochmal vielen Dank