Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Zelle leeren, wenn bestimmtest Datum erreicht ist


Betrifft: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Timo
Geschrieben am: 25.01.2019 09:22:48

Hallo zusammen, 😊

ich bin überfragt, liegt wohl daran, dass ich Neuling mit VBA bin.
Vielleicht könnt ihr mir ja helfen.

Ich habe eine Datei mit folgenden Daten:
Spalte U = Datum(Bsp. 31.07.2019)
Spalte K bis P = Private Daten

Ich möchte gerne, wenn das Datum in U7 der 31.07.2019 ist, dass die Zellen K7 – P7 automatisch am 31.07.2019 geleert (nicht gelöscht) werden und die Zellen K7 – P7 auch am 31.07.2019 automatisch gefärbt werden (z. B. Grau, Farbindex 16).

Das ganze dann für alle Zeilen.

Ist es überhaupt möglich?

Freue mich auf eure Antworten.

  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Daniel M.
Geschrieben am: 25.01.2019 10:20:55

Hallo Timo,

versuchs mal so:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Loeschdatum As Date

Dim cell As Range
Dim Check As Boolean

For Each cell In Range(Cells(1, "U"), Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, "U"))
   Loeschdatum = Cells(cell.Row, "U")
   If Loeschdatum <= Date Then
      With Range(Cells(cell.Row, "K"), Cells(cell.Row, "P"))
         .Clear
         .Interior.ColorIndex = 16
      End With
      Check = True
   End If
Next cell

If Check = True Then MsgBox "Daten mit Löschdatum Heute und früher wurden gelöscht"

End Sub
Das Ganz musst du unter "Diese Arbeitsmappe" einfügen. In diesem Fall führt er den Code vor dem Speichern der Tabelle aus. Du könntest das Event natürlich auch ändern auf Workbook_Open oder was auch immer am Meisten Sinn macht.

Gruß
Daniel


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Timo
Geschrieben am: 25.01.2019 11:49:00

Hallo Daniel,

vielen, vielen Dank für deine sehr schnelle Antwort ;)
Leider tut sich nichts, anscheindend speichert Excel das Makro auch nicht.

Ich bin wie folgt vorgegangen:

Entwicklertools\Code\Makros
- - - Beliebigen Makroname eingegeben
- - - Makros in "Diese Arbeitsmappe" ausgewählt
- - - Erstellen
- - - - Automatisch eingefügten Code entfernt
- - - - Dein Code eingefügt
- - - - Gespeichert
- - - - Geschlossen über "X"

Über Datei\Speichern habe ich es als ".xlsm" gespeichert und geschlossen.

Nach erneuten öffnen, sind die Zellen noch gefüllt.

Nach Überprüfung unter Entwicklertools\Code\Makros\Diese Arbeitsmappe ist auch kein Makro aufgezeichnet.

Vielleicht kannst du mir weiter behilflich sein :)

Danke und Gruß
Timo


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Daniel M.
Geschrieben am: 25.01.2019 12:00:13

Nicht ganz, dann speichert er das Makro trotzdem in einem Modul.

Speicher am besten deine Tabelle schon vorher als .xlsm ab. Dann öffnest du mit Alt+F11 den VBA Editor. Links oben siehst du die aktiven Arbeitsmappen. Unter deiner Arbeitsmappe siehst du unter Microsoft Excel Objekte ein Feld "DieseArbeitsmappe". Da gehst du rauf und kopierst den Code rein.
Der Code wird jetzt ausgeführt, sobald du die Tabelle speicherst.

Versuch noch mal.


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Timo
Geschrieben am: 25.01.2019 12:46:27

Hallo Daniel,

danke für deine weitere Rückmeldung. Ich hoffe ich stelle mich jetzt nicht zu d*** an. Leider sehe ich im Editor keine aktiven Arbeitsmappen - Screenshot anbei - und wo finde ich bzw. was meinst du mit unter Microsoft Excel Objekte ein Feld "DieseArbeitsmappe".




  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Daniel M.
Geschrieben am: 25.01.2019 12:50:02

Unter Ansicht müsstest du dann erstmal den Projekt-Explorer und den Code anzeigen. Dann sollte es offensichtlicher werden.


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Timo
Geschrieben am: 25.01.2019 13:08:42

Hallo Daniel,

danke :)

Das Problem ist leider immer noch nicht gelöst

"Laufzeitfehler '13': Typen unverträglich"

Loeschdatum = Cells(cell.Row, "U") wird gelb markiert.


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Daniel M.
Geschrieben am: 25.01.2019 13:52:52

In Uwes Lösung sieht man, dass die Variable auch überflüssig ist (keine Ahnung warum der Fehler hochkommt). Lösch einfach die Zeile und ändere die If Abfrage:

For Each cell In Range(Cells(1, "U"), Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, "U"))
   If cell <= Date Then
      With Range(Cells(cell.Row, "K"), Cells(cell.Row, "P"))
         .Clear
         .Interior.ColorIndex = 16
      End With
      Check = True
   End If
Next cell



  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: UweD
Geschrieben am: 25.01.2019 14:19:34

Hallo Daniel

der Fehler tritt auf, wenn die Zelle Text enthält (Überschrift)

Da du Loeschdatum als Datum definiert hast.

Deshalb arbeite ich nur die Zellen mit Zahlen ab SpecialCells(xlCellTypeConstants, 1)
(Prüfe natürlich vorher ob überhaupt welche da sind)


LG UweD


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Daniel M.
Geschrieben am: 25.01.2019 14:22:16

Danke Uwe, das mit den SpecialCells wird ich mir auf jeden Fall merken!


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: UweD
Geschrieben am: 25.01.2019 13:22:57

Hallo


hier noch eine Lösung

Muss in DieseArbeitsmappe

Microsoft Excel Objekt DieseArbeitsmappe

Option Explicit 
 
Private Sub Workbook_Open() 
    Dim RNG1 As Range, RNG2 As Range, Zeile, i As Long 
     
    Set RNG1 = Columns(21) ' Spalte U 
    Set RNG2 = Columns(11).Resize(, 6) 'K:P 
     
    If WorksheetFunction.Count(RNG1) > 0 Then 
        'Zahlen in Spalte vorhanden 
        For Each Zeile In RNG1.SpecialCells(xlCellTypeConstants, 1) 
            'Wenn kleiner/gleich heute dann weg 
            If Zeile <= Date Then 
                With Intersect(Zeile.EntireRow, RNG2) 
                    .ClearContents 'leeren 
                    .Interior.ColorIndex = 16 'färben 
                    i = i + 1 
                End With 
            End If 
        Next 
        MsgBox i & " x Datensatz gelöscht" 
    Else 
        MsgBox "Keine Daten gelöscht" 
    End If 
End Sub 

LG UweD


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Daniel M.
Geschrieben am: 25.01.2019 13:56:13

Schöne Lösung Uwe!
Warum benutzt du Intersect für die Auswahl der zu leerenden Zellen? Freue mich immer drüber, dazuzulernen!


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: UweD
Geschrieben am: 25.01.2019 14:23:31

Hallo nochmal

Ich arbeite gerne mit Variablen, die ich im Kopf angebe. Bei Änderungswünschen muss man dann nur an einer Stelle suchen. Also in dem Fall die beiden Spaltenbereiche

Interscet ist dabei die Schnittmenge aus der aktuellen Zeile mit den festgelegten Spalten


LG UweD


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: Daniel M.
Geschrieben am: 25.01.2019 14:31:19

Ja das klingt sinnvoll, besser gleich richtig machen.

Intersect war mir prinzipiell bekannt aber in dem Zusammenhang nicht wirklich in den Sinn gekommen. Aber stimmt, das ist dann natürlich sehr flexibel wenn plötzlich Änderungswünsche auftauchen (was ja offensichtlich regelmäßig der Fall ist...).

Grüße und Danke nochmal


  

Betrifft: AW: Zelle leeren, wenn bestimmtest Datum erreicht ist von: t.re@mail.de
Geschrieben am: 28.01.2019 07:37:09

Hallo,

vielen Dank euch beiden :)
Ihr habt mir sehr geholfen.

Den Code von Uwe konnte ich problemlos einfügen und es funktioniert ;)

Vielen Dank!

Grüße