Microsoft Excel

Herbers Excel/VBA-Archiv

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

teilausgefüllte Zeilen eín- und ausblenden

Betrifft: teilausgefüllte Zeilen eín- und ausblenden von: Holm
Geschrieben am: 23.10.2014 18:46:46

Hallo liebes Forum,

ich hab wieder einmal eine besondere Aufgabe:

Ausgangslage:

Spalte A Artikelnummer
Spalte B Bezeichnung
Spalte C und D Mengen
Spalte E Palettennummer (1-150)
Spalte F Bemerkung

Das Ziel:

In der "Originalzeile" der Palette 1 würde ich in der Zelle (E) eine Schaltfläche platzieren wollen.
Beim Betätigen der Schaltfläche soll folgendes passieren:
Unter der Originalzeile 1 soll eine neue leere Zeile eingefügt werden, nur in Spalte E (Palettennummer) soll schon eine 1 stehen.
Hinter dieser "neu eingefügten" 1 (in Spalte E) soll nach Möglichkeit auch eine Schaltfläche sein, durch dessen Betätigung diese Zeile (nach Bestätigungsabfage) wieder löschen lässt.
Man soll also durch Klicken der Schaltfläche der Originalzeile neue leere Zeilen mit der gleichen Palettennummer hinzufügen können (soll also für alle Originalzeilen von 1-150 machbar sein) und die so neu geschaffenen Zeilen sollen aber auch per einen Klick jederzeit bei Bedarf wieder löschbar sein.

Könnte also so aussehen, dass zB die Palettenummer 1 manchmal nur eine Zeile hat, die Palettennummer 2 mal 4 Zeilen usw....und je nach Bedarf änderbar.
Ideal wäre noch eine Art Löschschutz der Originalzeile jeder Palettennummer.

Vielen Dank für gute Ideen.

Grüße Holm

  

Betrifft: AW: teilausgefüllte Zeilen eín- und ausblenden von: fcs
Geschrieben am: 24.10.2014 07:52:43

Hallo Holm,

eine Beispieldatei wäre hier hilfreich.

Ich würde hier aber nicht mit Schaltflächen arbeiten, sondern mit Maus-Doppelklick (oder auch Rechte-Maus-Klick) in die Zellen mit den Palettennummern.

Gruß
Franz

'Makro unter dem Tabellenblatt mit den Eingaben
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.Column = 5 Then 'Spalte E
        If Target.Row > 1 Then
            If Application.WorksheetFunction.CountA(Range(Cells(Target.Row, 1), _
                Cells(Target.Row, 6))) = 1 Then
                'Neue Zeile mit Paletten-Nummer löschen
                If Target.Offset(-1, 0).Value = Target.Value Then
                    Target.EntireRow.Delete
                End If
                Cancel = True
            Else
                'Leerzeile mit Palettennummer einfügen
                Target.Offset(1, 0).EntireRow.Insert
                Target.Offset(1, 0).Value = Target.Value
                Cancel = True
            End If
        End If
    End If
End Sub




  

Betrifft: AW: teilausgefüllte Zeilen eín- und ausblenden von: Holm
Geschrieben am: 25.10.2014 18:04:43

Hallo Franz,

super danke, das ist (fast) genau das was ich suche!! :-)))

Nur 2 kleine "Schönheitsfehler":

Zum einem:

Ich kann per Doppelklick auf die Palettennummer nur "leere" Zeilen löschen (würde auch ausgefüllte löschen wollen)
Dafür würde es reichen, wenn ich nur auf eine Palettennummer jeder Zahl klicken müsste, um eine neue leere Zeile einzufügen. (Geht ja mit deinem Code jetzt per Doppelklick auf JEDE Zelle der gleichen Palettenummer)

Zum Zweiten:

Mit

Sub Worksheet_Change(ByVal target As Range)
    Set target = Intersect(target, Range("C:D"))
    If target Is Nothing Then Exit Sub
    Cells(target.Row, 8) = Now
    Set target = Nothing
End Sub
überwache ich die Änderungen in Spalten C+D, wenn ich jetzt leere (oder dann vielleicht auch volle) Zeilen per Doppelklick lösche, ändert sich in der sich darunter stehenden und damit dann eine Zeile nach oben rutschenden Zeile das Datum, obwohl in Spalte C und D sich ja dort eigentlich gar nix geändert hat.

Danke im Voraus!

Ihr seid genial....


  

Betrifft: AW: teilausgefüllte Zeilen eín- und ausblenden von: fcs
Geschrieben am: 26.10.2014 07:37:40

Hallo Holm,

ich hab die Schönheitsfehler beseitigt.

Eine neue Zeile wird nur noch eingefügt, wenn man in der 1./obersten Zeile eines Zellblock mit der gleichen Paltten-Nr. auf die Zelle mit der Paletten-Nr. doppelt klickt. Ein Doppelklick in den weiteren Zeilen startet den Löschvorgang - bei Zeilen mit Inhalten wird noch eine Sicherheitsabfrage angezeigt.

Der Konflikt mit dem Change-Ereignis, das auf Mengeneingaben reagiert wird dadurch verhindert, das die Ereignismakros vorübergehend deaktiviert werden.

Gruß
Franz

'Makro unter dem Tabellenblatt mit den Eingaben
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.Column = 5 Then 'Spalte E
        If Target.Row > 1 Then
            Application.EnableEvents = False
            If Target.Offset(-1, 0).Value = Target.Value Then
                If Application.WorksheetFunction.CountA(Range(Cells(Target.Row, 1), _
                    Cells(Target.Row, 6))) = 1 Then
                    'Neue Zeile mit Paletten-Nummer löschen
                    Target.EntireRow.Delete
                Else
                    If MsgBox("Zeile " & Target.Row & " löschen?", _
                            vbQuestion + vbOKCancel + vbDefaultButton2, _
                            "Zeile löschen") = vbOK Then
                        'Zeile mit Inhalten löschen
                        Target.EntireRow.Delete
                    End If
                End If
                Cancel = True
            ElseIf Target.Offset(-1, 0).Value <> Target.Value Then
                'Leerzeile mit Palettennummer einfügen
                    Target.Offset(1, 0).EntireRow.Insert
                    Target.Offset(1, 0).Value = Target.Value
                    Cancel = True
            End If
            Application.EnableEvents = True
        End If
    End If
End Sub



  

Betrifft: AW: teilausgefüllte Zeilen eín- und ausblenden von: Holm
Geschrieben am: 26.10.2014 16:02:40

Hallo Franz,

vielen vielen Dank für die Hilfe! Einfach super! Und das zum Sonntag! Hut ab!!
Wie du vielleicht denken kannst, kommt man dann immer auf neue Ideen, um es einfach so perfekt wie möglich zu machen.....
Nämlich:

in Spalte B ist ein Sverweis:

=WENN(ISTNV(SVERWEIS(A2;Tabelle3!A:B;2;FALSCH));"";(SVERWEIS(A2;Tabelle3!A:B;2;FALSCH)))

wenn ich nun die erste Zelle einer Palettennummer zum Erstellen einer neuen Zeile doppelklicke, müsste dieser SVerweis automatisch in jeder neu erstellten Zeile von alleine auftauchen. Geht das?

Als nächstes:

Das Doppelklicken aller weiterer Palettennummern außer der obersten zum Löschen ist perfekt!
Wäre es möglich, durch Doppelklicken der Spalte A vorher den Inhalt der Zeile außer Spalte E (Palettennummer) zu löschen, mit Sicherheitsabfrage? Das Löschen der gesamten Zeile wie du es eingestellt hast, kann so bleiben!

Dann hätte ich gerne noch in Spalte H die Summe aller Zellen aus Spalten C und D, in denen sich in Spalte E die gleiche Palettennummer befindet.
Sozusagen sehe ich in der obersten Zeile einer Palettennumemr, wieviel Kartons sich insgesamt auf der Palette (zB 1) befinden. Egal, ob sich nur 1 oder 3 oder 7 (verschiedene Zeilen bzw) Artikel auf der Palette befinden.
Müsste sich natürlich auch von alleine erweitern, wenn neue Zeilen mit einer Palettennummer hinzugefügt werden.
Danke im Voraus!!! :-)


  

Betrifft: AW: teilausgefüllte Zeilen eín- und ausblenden von: fcs
Geschrieben am: 27.10.2014 12:41:31

Hallo Holm,

das Makro entwickelt sich langsam zu einem Roman.

Formel in H2 zur Berechnung der Anzahl Verpackungen:

=WENN(BEREICH.VERSCHIEBEN(E2;-1;0)=E2;"";SUMMENPRODUKT((E2=$E$2:$E$149)*$C$2:$D$149))

Die 149 in der Formel musst du entsprechend der Zeilenzahl in deiner Liste anpassen. Diese Formel dann in Spalte H kopieren.

Denke auch daran, das du im Worksheet_Change-Makro die Nummer der Spalte anpassen musst, in die Datum-Zeit eingetragen werden.

Gruß
Franz
'Makro unter dem Tabellenblatt mit den Eingaben
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Zeile As Long
    Dim Statuscalc As Long
    Select Case Target.Column
      Case 1  'Spalte A
          If MsgBox("In Zeile " & Target.Row & " Werte außer Paletten-Nr. löschen?", _
                  vbQuestion + vbOKCancel + vbDefaultButton2, _
                  "Inhalte in Zeile löschen") = vbOK Then
              'Makrobremsen lösen
              With Application
                .EnableEvents = False
                Statuscalc = .Calculation
                .Calculation = xlCalculationManual
                .ScreenUpdating = False
              End With
              'In Zellen mit Werten außer Palletten-Nr. den Inhalt löschen
              With Cells(Target.Row, 1)
                .Offset(0, 0).ClearContents
                .Offset(0, 2).ClearContents
                .Offset(0, 3).ClearContents
                .Offset(0, 5).ClearContents
                .Offset(0, 6).ClearContents
                .Offset(0, 8).ClearContents
              End With
              'Makrobremsen zurücksetzen
              With Application
                .EnableEvents = True
                .Calculation = Statuscalc
                .ScreenUpdating = True
              End With
              Cancel = False
          End If
      Case 5  'Spalte E
        If Target.Row > 1 Then
            'Makrobremsen lösen
            With Application
              .EnableEvents = False
              Statuscalc = .Calculation
              .Calculation = xlCalculationManual
              .ScreenUpdating = False
            End With
            If Target.Offset(-1, 0).Value = Target.Value Then
                'Prüfen, ob außer Paletten-Nr. und Formeln ander Zellen ausgefüllt sind
                If Application.WorksheetFunction.CountA(Range(Cells(Target.Row, 1), _
                    Cells(Target.Row, 6))) = 2 Then
                    'Neue Zeile mit Paletten-Nummer löschen
                    Target.EntireRow.Delete
                Else
                    If MsgBox("Zeile " & Target.Row & " löschen?", _
                            vbQuestion + vbOKCancel + vbDefaultButton2, _
                            "Zeile löschen") = vbOK Then
                        'Zeile mit Inhalten löschen
                        Target.EntireRow.Delete
                    End If
                End If
                Cancel = True
            ElseIf Target.Offset(-1, 0).Value <> Target.Value Then
                'Leerzeile mit Palettennummer einfügen
                Target.Offset(1, 0).EntireRow.Insert
                Target.Offset(1, 0).Value = Target.Value
                'Formel in Spalte B kopierem
                Cells(Target.Row, 2).Copy Cells(Target.Row + 1, 2)
                'Formel in Spalte H kopieren/neu erstellen
                Zeile = Target.Row
                If Zeile + 1 = Cells(Rows.Count, 2).End(xlUp).Row Then
                  'Neuer Eintrag am Ende der Liste - alle Formeln in H ab Zeile 2 neu erstellen
                  Range(Cells(2, 8), Cells(Zeile + 1, 8)).FormulaR1C1 = _
                      "=IF(OFFSET(RC[-3],-1,0)=RC[-3],"""",SUMPRODUCT((RC[-3]=R2C5:R" _
                      & Zeile + 1 & "C5)*R2C3:R" & Zeile + 1 & "C4))"
                Else
                  'Formel in Spalte H kopieren
                  Cells(Zeile, 8).Copy Cells(Zeile + 1, 8)
                End If
                
                Cancel = True
            End If
            'Makrobremsen zurücksetzen
            With Application
              .EnableEvents = True
              .Calculation = Statuscalc
              .ScreenUpdating = True
            End With
        End If
    End Select
End Sub




  

Betrifft: AW: teilausgefüllte Zeilen eín- und ausblenden von: Holm
Geschrieben am: 29.10.2014 19:51:22

Hallo Franz,

danke, klappt alles super!! :-)))

Einen ("Wunsch") hätte ich dann doch noch....

in Spalte H habe ich noch die Formel

=SUMMEWENN($A$2:$A$991;A2;$C$2:$D$991)

um von Artikeln mit gleicher Nummer in Spalte A die Gesamtsumme zu bekommen.
Allerdings soll die Summe der gleichen Artikel aus Spalte C UND D ermittelt werden, so habe ich den Bereich auch eingegeben, es wird aber komischerweise nur die Mengen aus Spalte C addiert.
Geht das auch noch irgendwie?

Danke im Voraus!

Holm


  

Betrifft: schau dir mal Summenprodukt an: von: Matze Matthias
Geschrieben am: 29.10.2014 21:16:47

Hallo Holm,

http://www.online-excel.de/excel/singsel.php?f=53

Gruß Matze


  

Betrifft: Hmm ich glaub damit lag ich falsch, von: Matze Matthias
Geschrieben am: 29.10.2014 21:34:04

nimm doch

=SUMMEWENN($A$2:$A$991;A2;$C$2:$C$991)+SUMMEWENN($A$2:$A$991;A2;$D$2:$D$991)

die Profis machen das bestimmt kürzer

Gruß Matze


  

Betrifft: AW: Hmm ich glaub damit lag ich falsch, von: Holm
Geschrieben am: 02.11.2014 14:58:33

Also,

jetzt bin ich irgendwie durcheinander....

habe alles bisher in einer älteren Version (also von den Daten her älter) der ganz normalen Datei ausprobiert und getestet.
Jetzt will ich alles in die aktuelle Originaldatei einbauen, bei den Makros bzw Codes klappt auch alles bestens.
Nur bei einer Formel hab ich Schwierigkeiten:

In H steht

=WENN(BEREICH.VERSCHIEBEN(E2;-1;0)=E2;"";SUMMENPRODUKT((E2=$E$2:$E$149)*$C$2:$D$149))

zum Berechnen der Summe aller Verpackungen pro Palette. (Palttennummern stehen in E)

Wenn ich diese Formel von der Testdatei in die Originaldatei kopiere (alle Spalten sind identisch),
steht #WERT! in der Zelle.

Hab es mehrmals kopiert; auch versucht, den Link hier aus dem Forum in die Zelle zu kopieren, funktioniert auch nicht.

Einzigster Unterschied:

Die Testdatei wurde mit Excel2010 erstellt, die Originaldatei ist bereits Excel 2013 (sind unterschiedliche PC). Das sollte doch aber keinen Unterschied machen??
Alle anderen Formeln sind ebenfalls kopiert und funktionieren.

Jemand ne Idee??

Grüße Holm


  

Betrifft: AW: Hmm ich glaub damit lag ich falsch, von: Holm
Geschrieben am: 02.11.2014 15:01:42

Also,

jetzt bin ich irgendwie durcheinander....

habe alles bisher in einer älteren Version (also von den Daten her älter) der ganz normalen Datei ausprobiert und getestet.
Jetzt will ich alles in die aktuelle Originaldatei einbauen, bei den Makros bzw Codes klappt auch alles bestens.
Nur bei einer Formel hab ich Schwierigkeiten:

In H steht

=WENN(BEREICH.VERSCHIEBEN(E2;-1;0)=E2;"";SUMMENPRODUKT((E2=$E$2:$E$149)*$C$2:$D$149))

zum Berechnen der Summe aller Verpackungen pro Palette. (Palttennummern stehen in E)

Wenn ich diese Formel von der Testdatei in die Originaldatei kopiere (alle Spalten sind identisch),
steht #WERT! in der Zelle.

Hab es mehrmals kopiert; auch versucht, den Link hier aus dem Forum in die Zelle zu kopieren, funktioniert auch nicht.

Einzigster Unterschied:

Die Testdatei wurde mit Excel2010 erstellt, die Originaldatei ist bereits Excel 2013 (sind unterschiedliche PC). Das sollte doch aber keinen Unterschied machen??
Alle anderen Formeln sind ebenfalls kopiert und funktionieren.

Jemand ne Idee??

Grüße Holm


 

Beiträge aus den Excel-Beispielen zum Thema "teilausgefüllte Zeilen eín- und ausblenden"