Pflichtzeile erstellen

[Seanen]

Hallo miteinander,

ich benötige für meine Seminarliste eine Pflichtzeile.
Das bedeutet, dass bei einer Eintragung in der Zeile, automatisch die Gesamte Zeile (Spalte von C bis M) ausgefüllt werden MUSS oder eben alle Zellen leer sein müssen. Andernfalls funktioniert das Speichern nicht. Da meine VBA-Kentnisse noch nicht allzu gut sind, verzeiht es mir wenn ich vermeintliche "dumme" Nachfragen stelle. Aktuell habe ich folgenden Funktion zusammengebastelt:

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim zeile As Long
    'zahl darf positiv wie negativ bis 2 mrd sein
    Dim OK As Boolean
    'ok ist wahr oder falsch
    OK = True
    'ok ist wahr
    For zeile = 2 To Tabelle12.Cells.SpecialCells(xlCellTypeLastCell).Row
    'für zeile = 2 (konstante) bis letzte zeile, for startet eine schleife
        If Tabelle12.Cells(zeile, 8).Value <> "" Then
        'wenn in tabelle 12 zeile 2 spalte 8 ein wert ungleich nichts steht dann -->
        
            If Tabelle12.Cells(zeile, 2).Value = "" Then OK = False 'Spalte B
            If Tabelle12.Cells(zeile, 3).Value = "" Then OK = False 'Spalte C
            If Tabelle12.Cells(zeile, 4).Value = "" Then OK = False 'Spalte D
            If Tabelle12.Cells(zeile, 5).Value = "" Then OK = False 'Spalte E
            If Tabelle12.Cells(zeile, 6).Value = "" Then OK = False 'Spalte F
            If Tabelle12.Cells(zeile, 7).Value = "" Then OK = False 'Spalte G
            If Tabelle12.Cells(zeile, 9).Value = "" Then OK = False 'Spalte I
            If Tabelle12.Cells(zeile, 10).Value = "" Then OK = False 'Spalte J
            If Tabelle12.Cells(zeile, 11).Value = "" Then OK = False 'Spalte K
            If Tabelle12.Cells(zeile, 12).Value = "" Then OK = False 'Spalte L
            If Tabelle12.Cells(zeile, 13).Value = "" Then OK = False 'Spalte M
            
        End If
    Next
    'schleife startet neu
    If Not OK Then
    'wenn nicht ok das ergebnis dann öffne eine messagebox mit folgendem inhalt der das speichern verhindert bis spalte b bis m befüllt wurden!
        MsgBox "Speichern nicht möglich - bitte füllen Sie alle Zellen Ihrer Anmeldung aus (Spalte B bis P)! Vergewissern Sie sich das alle anderen Zellen nach Ihrer aktuellen Eintragung leer sind!" & ActiveSheet.Name, vbOKCancel + vbInformation, "Information"
        Cancel = True
    End If
End Sub

Aktuell ist leider nur eine Zelle als Pflichtfeld definiert und ich möcht zwecks Nachvollziehbarkeit die ganze Zeile ausgefüllt haben! habt ihr noch Anregungen oder Ideen wie ich etwas besser/anders machen könnte, bin ich dafür jederzeit offen!

Vielen Dank schon einmal für eure Hilfe!

VG

Christian

[Werner.M]

Hallo Christian,

so:

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim raZelle As Range

With Worksheets("Tabelle1") 'Blattname anpassen
   For Each raZelle In .Range(.Cells(2, 3), .Cells(.Cells(.Rows.Count, 3).End(xlUp).Row, 3))
       If raZelle <> "" Then
           If WorksheetFunction.CountBlank(raZelle.Resize(, 10)) > 0 Then
               Cancel = True
               MsgBox "Speichern ist nicht möglich" & vbLf _
               & vbLf & "In Zeile " & raZelle.Row & " sind nicht alle Pflichtfelder befüllt."
               Exit For
           End If
       End If
   Next raZelle
End With

End Sub

Gruß Werner

[MisterBurns]

Hallo Christian,

das kannst du mittels der ANZAHL2-Funktion abfragen:

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim zeile As Long
With Sheets("Tabelle1")
   For zeile = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
       If Application.WorksheetFunction.CountA(.Range(.Cells(zeile, "B"), .Cells(zeile, "M"))) < 12 Then
           MsgBox "Es wurden nicht alle Zeilen vollständig ausgefüllt!"
           Cancel = True
           End
       End If
   Next
End With
End Sub

Eine Überlegung wäre auch, dass du eine bedingte Formatierung einfügst, die mittels der gleichen Funktion die jeweilige Zeile rot färbt, solange sie nicht vollständig befüllt ist.

[Der Steuerfuzzi]

Hallo,

mit der Funktion Anzahl2 kann man ermitteln wie viele Zellen gefüllt sind, d.h. man kann prüfen, ob ein Bereich leer ist (Ergebnis ist 0) oder ganz gefüllt ist (Ergebnis entspricht der Anzahl an Zellen).

In VBA kannst Du das mit WorksheetFunction.CountA(Range) machen, also bei dir z. B. für die Spalten B bis M:

Code:

...
i = WorksheetFunction.CountA(Tabelle12.Cells(zeile, 2).Resize(,12))
if i > 0 and i < 12 then 'Nicht leer und nicht ganz gefüllt!
     OK = False
     Exit For
end if
...

[Kuwer]

Hallo Christian,

z.B. so:

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Zeile As Long
    'zahl darf positiv wie negativ bis 2 mrd sein
    With Tabelle12
        For Zeile = 2 To .Cells.SpecialCells(xlCellTypeLastCell).Row
        'für zeile = 2 (konstante) bis letzte zeile, for startet eine schleife
            If Application.CountBlank(.Cells(Zeile, 2).Resize(1, 12)) Then
            'wenn auch nur eine Zelle leer ist dann -->
                MsgBox "Speichern nicht möglich - bitte füllen Sie alle Zellen Ihrer Anmeldung aus (Spalte B bis M)! Vergewissern Sie sich das alle anderen Zellen nach Ihrer aktuellen Eintragung leer sind!" & ActiveSheet.Name, vbOKCancel + vbInformation, "Information"
                Cancel = True
                Exit For
            End If
        Next
    End With
End Sub

Gruß Uwe

[Seanen]

Hallo ihr Lieben,

zunächst einmal ein riesen Dankeschön an ALLE! Ich habe es direkt mit der Funktion von Werner ausprobiert und die funktioniert super! Insbesondere auch mit dem Hinweisfeld in welcher Zeile Eingaben fehlen!

@Berni: Die bedingten Formatierungen haben schon andere Aufgaben wie bspw. Farbe XY bei Anmeldung, bei Bezahlung, Stornierung u.ä. Sonst hätte ich die Idee auch gerne mit aufgegriffen!

Also nochmal vielen Lieben Dank euch allen für die schnelle und unkomplizierte Hilfe!!!

LG

Christian

[schauan]

Hallöchen,

noch ein kleiner Vorschlag. Bilde in der Schleife einen String mit den Zeilen, wo was fehlt und gebe die Meldung anschließend aus.
Im Prinzip in der Schleife

strMsg = strMsg & ", "

und erst am Ende

Code:

MsgBox "Speichern ist nicht möglich" & vbLf _
              & vbLf & "In Zeile(n) " & strMsg & " sind nicht alle Pflichtfelder befüllt."

Sofern der String zu lang wird, könnte man ihn noch trennen ...

[Seanen]

Hallo,

ich habe nochmal eine Verständnis-Rückfrage zum Code von Werner:

 For Each raZelle In .Range(.Cells(2, 2), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, 2))

Ich verstehe den Code so:
For Each startet die Schleife mit der Variablen raZelle. Danach start in Zelle (2,2) und schaue an wie viele Zeilen es insgesamt gibt. Springe dann von unten in die Zelle in der was steht (letzte zeile mit inhalt,2)

If raZelle <> "" Then

           If WorksheetFunction.CountBlank(raZelle.Resize(, 11)) > 0 Then

Dann wird geprüft ob raZelle einen Inhalt hat (von B bis m) und sagt wenn mehr als 0 Zelle einen Inhalt haben, ist ein speichern nicht möglich.
Ist meine Interpretation so richtig?



Wenn ja:
Ich würde gerne das er nicht nur in der Zelle mit dem letzten Eintrag prüft sondern noch einen darüber hinaus.
Das Bedeutet:
Eintrag in 7,2 --> Prüfung Zeile 7
Nun möchte ich das sicherheitshalber auch Zeile 8 und vllt auch 9 geprüft wird, falls einer verpennt hat, das Quartal anzugeben (denn hier nach werden Auswertungen gefahren)

Ist das möglich?

Vielen Dank schon mal für eure Antworten :)

Christian

[Seanen]

Hallo ihr Lieben,

hat vllt noch jemand eine Idee? Gerne eläutere ich mein Problem sonst noch einmal oder ergänze die Beispieldatei entsprechend.

LG

Christian

[schauan]

Hallöchen,

For Each raZelle In .Range(.Cells(2, 2), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, 2))

raZelle sind die einzelnen Zellen im betrachteten Bereich in Spalte B.
Betrachtet wird der Bereich ab B2 bis zum Ende des ausgefüllten Bereichs.
Wenn Du eine oder zwei Zeilen mehr willst, dann addiere einfach 1 oder 2 zu ...Row