Office: (Office 2016) ListBox: Eintrag bearbeiten, Duplikatprüfung

Hey :)

Ich habe ein UserForm mit ListBox und den Schaltflächen "Hinzufügen" und "Bearbeiten" und würde gerne, dass doppelte Einträge unterbunden werden.

Für das Hinzufügen funktioniert das auch soweit und es werden nur unikate Werte in das Tabellenblatt geschrieben, allerdings habe ich bei der "Bearbeiten"-Schaltfläche das Problem, dass auch der eigene Wert (Barcode) gezählt wird.

Wenn ich also z.B. beim Eintrag mit dem Barcode 123 einen Tippfehler in der Artikelbezeichnung korrigieren möchte und dann auf "Bearbeiten" klicke, wird dies nicht gestattet.

Das ist natürlich nicht Sinn der Sache, ich möchte dort halt nur verhindern, dass während des Bearbeitens auf eine Barcode-Nummer gewechselt werden kann, die bereits im Tabellenblatt vorhanden ist.

Ich habe versucht, es nach dem Muster, das Beverly mir für die "normalen" Tabellen gezeigt hat, zu schaffen, bekomme es aber leider nicht hin.

Das hier ist der Code vom "Bearbeiten"-Button (ohne Duplikatprüfung), bzw. habe ich die Datei mit angehangen.

Code:

Private Sub changeButton_Click() 'Bearbeiten-Schaltfläche

Dim lngZeile As Long 'Relevante Zeile

    If BestandBox.ListIndex = -1 Then
    MsgBox "Wählen Sie einen Eintrag", vbExclamation
    Exit Sub
    End If

    Sheets("Bestand").Range("B:B").Find(Me.BestandBox.Column(1, Me.BestandBox.ListIndex), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True).Activate

    lngZeile = ActiveCell.Row

    Cells(lngZeile, 2) = txtBarcode.Text
    Cells(lngZeile, 1) = txtDatum.Caption 'Als zweites, um tatsächliches Eingangsdatum zu erhalten (WorkSheet_Change)
    Cells(lngZeile, 3) = txtHersteller.Text
    Cells(lngZeile, 4) = txtArtBez.Text
    Cells(lngZeile, 5) = cmbWarengruppe.Text
    Cells(lngZeile, 6) = cmbStandort.Text
    Cells(lngZeile, 7) = cmbStatus.Text


    MsgBox "Eintrag wurde aktualisiert", vbOKOnly, "Bearbeiten"
    BestandBox.List = Sheets("Bestand").Range("A2:G" & [A65536].End(3).Row).Value 'ListBox aktualisieren

End Sub

Über Hilfe würde ich mich freuen :)

Liebe Grüße.

Edit: Ich habe vergessen, das automatische Öffnen des UserForm auszuschalten Also, falls ihr euch die Datei anschauen wollt, bitte oben rechts auf "Tabellenblatt" klicken und dann auf "Beenden". :)

 

Hi Nina,

so richtig verstehe ich dein Problem nicht - beschreibe doch mal, was man genau machen muss, um dieses auszulösen.

Bis später,
Karin

 

Hallo,

ja, sehr gerne.

Hier sind die Schritte zum Reproduzieren:

1. Eintrag mit Barcode 12345 erstellen.
2. Eintrag mit Barcode 12346 erstellen.
3. Eintrag 12345 in der Listbox auswählen, den Barcode auf 12346 abändern und auf "Bearbeiten" klicken.

Nun gibt es zwei identische Barcodes im "System", man soll allerdings beim "Bearbeiten" nur auf einen noch nicht vorhanden Barcode umändern können.

Mit der Duplikatprüfung, wie sie beim "Hinzufügen" geschieht, habe ich es für das "Bearbeiten" nicht hinbekommen, da man somit einen Eintrag überhaupt nicht mehr abändern konnte, weil die eigene Barcodenummer immer auch schon als Duplikat gewertet wird.

Notfalls könnte ich die Barcode-Textbox auch während des Anzeigens eines vorhanden Eintrags deaktivieren (wie bei der Datum-Textbox), dann müsste man allerdings, falls man sich bei der Barcode-Eingabe während des Hinzufügens vertippt hätte, den gesamten Eintrag löschen und neu eingeben.

Ich hoffe, ich konnte mich jetzt verständlicher ausdrücken. :)


Liebe Grüße.

 

Hi Nina,

prüfe einfach mittels Application.CounIf (ZÄHLENWENN()), ob der Eintrag bereits vorhanden ist (blau formatierter Code):

Code:

    ' ... hier dein anderer Code

    Dim lngZeile As Long 'Relevante Zeile

    If BestandBox.ListIndex = -1 Then
        MsgBox "Wählen Sie einen Eintrag", vbExclamation
        Exit Sub
    End If

    [COLOR=#ff0000]Sheets("Bestand").Columns(2).Cells(BestandBox.ListIndex + 1, 2).Activate[/COLOR]
    [COLOR=#0000ff]If Application.CountIf(Columns(2), txtBarcode) = 0 Then[/COLOR]
        lngZeile = ActiveCell.Row
        Cells(lngZeile, 2) = txtBarcode.Text
        [COLOR=#008000]Cells(lngZeile, 1) = Date[/COLOR]
        Cells(lngZeile, 3) = txtHersteller.Text
        Cells(lngZeile, 4) = txtArtBez.Text
        Cells(lngZeile, 5) = cmbWarengruppe.Text
        Cells(lngZeile, 6) = cmbStandort.Text
        Cells(lngZeile, 7) = cmbStatus.Text
    
        MsgBox "Eintrag wurde aktualisiert", vbOKOnly, "Bearbeiten"
        BestandBox.List = Sheets("Bestand").Range("A2:G" & [A65536].End(3).Row).Value 'ListBox aktualisieren
[COLOR=#0000ff]    Else
        MsgBox "Barcode " & txtBarcode & " bereits vorhanden"
    End If[/COLOR]

Die relevante Zeile findest du viel einfacher, indem du den ListIndex direkt verwendest, ohne den Barcode in Spalte B mittels Find zu suchen (rot formatierter Code).



Weshalb lädst du beim Starten des UserForms das aktuelle Datum nicht in dein Label?

Code:

    txtDatum = Date

Da du beim Erfassen und Bearbeiten von Einträgen sowieso das aktuelle Datum verwendest und die Daten anscheinend offensichtlich nur über das Interface erfassen/bearbeiten willst und nicht von Hand im Tabellenblatt, würde ich das Eintragen des Datums nicht im Worksheet_Change-Ereignis machen, sondern im Code des betreffenden CommandButton im UserForm (grün formatierter Codeteil). Diesen Teil müsstest du dann auch für das Erfassen von neuen Einträgen entsprechend ergänzen.

Bis später,
Karin

 

Hey,

danke für deine Antwort :)

Ich hatte erst in etwa den gleichen Code wie du benutzt, um die relevante Zeile zu finden, das gab allerdings Probleme bei der Duplikatsuche ("Hinzufügen"-Button),
beim Bearbeiten und auch beim Entfernen von Einträgen, weil nicht der exakte, unikate Wert (Barcode) gefunden wurde.

Wenn z.B. in Zeile 2 Barcode 1234 und in Zeile 3 Barcode 123 stand und ich den Eintrag Zeile 3 bearbeitet habe, dann hat er die Werte aus Zeile 3
für Zeile 2 mit übernommen.


Mit dem Code für die Duplikatsuche ("Bearbeiten"-Button) habe ich leider auch kein Glück. Das Problem, das der eigene Wert als Duplikat gewertet wird, besteht
weiterhin und somit ist eine Bearbeitung von Einträgen (Standortwechsel, Tippfehler) nicht möglich.

Wenn ich von Application.CountIf(Columns(2), txtBarcode) = 0 auf = 1 schalte, dann kann ich zwar wieder Einträge bearbeiten, allerdings eben auch wieder ein Barcode-Duplikat schaffen.

Wenn das aber zu kompliziert ist, dann lasse ich die Barcode-Textbox im "Bearbeitungs-Modus" halt echt einfach auf Disabled schalten und man muss eben während des Hinzufügens aufpassen, direkt den richtigen Barcode einzugeben :p


Das aktuelle Datum lasse ich nicht direkt in die Datum-Textbox schreiben, damit, wenn man Einträge älteren Datums bearbeitet (Tippfehler,Standortwechsel), das tatsächliche Eingangsdatum bestehen bleibt. Irgendwie ist mir Worksheet_Change als erstes dazu eingefallen aber wenn das nicht ideal sein sollte, werde ich das in den "Hinzufügen"-Code mit reinnehmen. :)

Liebe Grüße.

 

Hi Nina,

ich habe mal 2 OptionButton ergänzt, mit denen man für den CommandButton "Bearbeiten" unterscheiden kann, ob man einen bereits vorhandenen Barcode oder die zu einem bereits vorhandenen Barcode existierenden Daten bearbeiten will - damit wird festgelegt, welche TextBoxen nicht aktiv sind und welche aktiv sind und geändert werden können. Davon ausgehend unterscheidet sich dann der Code für das Bearbeiten, sprich Speichern der geänderten Inhalte.

Bis später,
Karin

 

Hey,

vielen Dank für deine Zeit!

Wenn du bei deiner Antwort-Datei eine der beiden unteren Einträge bearbeitest, siehst du, welches Problem ich vorher gehabt habe;
der Zeileninhalt wird in die darüber liegende Zeile mitkopiert.

Optisch wären die beiden OptionsButton dort natürlich auch nicht gerade der Renner :p

Wäre es nicht irgendwie möglich, dass CountIf die aktivierte Zeile (lngZeile) aus der Suche ausschließt, bzw. sie umgeht, indem
bis lngZeile - 1 und dann erst wieder ab lngZeile + 1 gesucht wird?

Oder, wenn der txtBarcode-Inhalt in lngZeile gefunden wird, soll diese ignoriert werden?

Ansonsten fange ich schon einmal an, mich mit der anderen Lösung anzufreunden, ich versuche jetzt erstmal das WorkSheet_Change Sub aufzulösen.

Liebe Grüße. :)

 

Hi,

im Code ist ein Fehler - es muss heißen + 2 und im Else-Zweig muss der Zellbezug auf den ListIndex bezogen werden:

Code:

    If optDaten Then
        Sheets("Bestand").Columns(2).Cells(BestandBox.ListIndex + 2, 2).Activate
        lngZeile = ActiveCell.Row
        Cells(lngZeile, 1) = Date
        Cells(lngZeile, 3) = txtHersteller.Text
        Cells(lngZeile, 4) = txtArtBez.Text
        Cells(lngZeile, 5) = cmbWarengruppe.Text
        Cells(lngZeile, 6) = cmbStandort.Text
        Cells(lngZeile, 7) = cmbStatus.Text
        MsgBox "Eintrag wurde aktualisiert", vbOKOnly, "Bearbeiten"
        BestandBox.List = Sheets("Bestand").Range("A2:G" & [A65536].End(3).Row).Value 'ListBox aktualisieren
    Else
        If Application.CountIf(Sheets("Bestand").Columns(2), txtBarcode) > 0 Then
            MsgBox "Barcode " & txtBarcode & " bereits vorhanden"
        Else
            Sheets("Bestand").Cells(BestandBox.ListIndex + 2, 2) = txtBarcode.Text
        End If
    End If

Ich persönlich finde es wichtig und übersichtlicher, den Bearbeiter darauf hinzuweisen, ob er den Barcode oder die Daten zum Barcode bearbeitet - aber das ist Ansichtssache.

Bis später,
Karin

 

Hi,

hier allerdings eine Lösung ohne OptionButton.

Bis später,
Karin

 

Hey,

vielen Dank dafür :)

Im Grunde ist das jetzt genau so, wie ich es haben wollen würde, allerdings gibt es nun das Problem, dass diese Methode für den ersten und den letzten Eintrag nicht funktioniert. Das ist dann natürlich nicht gerade wasserdicht :p

Ich würde ja jetzt sagen, wenn du da nicht noch vom Geistesblitz getroffen wirst, dass die Zugriffsrechte für das Barcode-Textfeld einfach nur während des Hinzufügens vergeben werden. Ich selbst werde das Problem wohl anders eher weniger lösen können :p

Liebe Grüße. :)

 

Hi,

Code:

    Dim lngLetzte As Long
    Dim lngAnzahl As Long
    With Worksheets("Bestand")
        .Columns(2).Cells(BestandBox.ListIndex + 2).Activate
        lngZeile = ActiveCell.Row
        lngLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
        If lngZeile = 2 Then
            lngAnzahl = Application.CountIf(.Range(.Cells(lngZeile + 1, 2), .Cells(lngLetzte, 2)), txtBarcode.Value)
        Else
            lngAnzahl = Application.CountIf(.Range(.Cells(2, 2), .Cells(lngZeile - 1, 2)), txtBarcode.Value) + Application.CountIf(.Range(.Cells(lngZeile + 1, 2), .Cells(lngLetzte + 1, 2)), txtBarcode.Value)
        End If
        If lngAnzahl = 0 Then
            .Cells(lngZeile, 1) = Date
            .Cells(lngZeile, 2) = txtBarcode.Value
            .Cells(lngZeile, 3) = txtHersteller.Text
            .Cells(lngZeile, 4) = txtArtBez.Text
            .Cells(lngZeile, 5) = cmbWarengruppe.Text
            .Cells(lngZeile, 6) = cmbStandort.Text
            .Cells(lngZeile, 7) = cmbStatus.Text
            MsgBox "Eintrag wurde aktualisiert", vbOKOnly, "Bearbeiten"
            BestandBox.List = Sheets("Bestand").Range("A2:G" & lngLetzte).Value 'ListBox aktualisieren
        Else
            MsgBox "Barcode " & txtBarcode & " bereits vorhanden"
        End If
    End With

Bis später,
Karin

 

Hey und Juhu :)

Das ist supercool jetzt, du bist klasse!

Vielen, vielen Dank, so gefällt es mir am besten.

Ich lasse jetzt noch den Barcode speichern und gebe ihn zurück, falls die neue Eingabe ein Duplikat sein sollte und nach "erfolgreichem" Bearbeiten wird jetzt auch die bearbeite Zeile in der Listbox wieder angewählt.

Die Eingabefelder haben jetzt auch Charakterlimitierung, die WorkSheet_Change Sub habe ich gestrichen (ging ja schnell :p) und das Navigieren-Feld ist auch fertig :)

Mit dem restlichen freien Platz weiß ich nichts anzufangen, aber so ist es ja auch eh schon genug.

Sollte ich die restlichen Sheets("Bestand").Range("B:B").Find(Me.txtBarcode.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True) auch noch durch deine Methode ersetzen? Wenn das effizienter ist, weil die Computer dort laufen sowieso schon am Limit, wenn mal auf "Speichern" geklickt wird :p

Anbei ist noch einmal der aktuelle Stand der Dinge zum Anschauen :)

Nochmals vielen Dank, liebe Grüße und gute Nacht :)

 

Hi,

an dem Code ließe sich noch einiges verbessern, was dein Projekt wesentlich beschleunigen würde - dazu müsste aber folgenes vorab geklärt werden: der rechte Teil deines UserForms, also alles was mit Suchen und Sortieren zu tun hat, ist der nur dazu gedacht, die Daten anzuzeigen oder sollen die selektierten Daten dabei auch geändert werden können? Von dieser Frage hängt ganz entscheidend ab, wie die Codeänderungen aussehen müssen.

Aber schon mal vorab einige Hinweise:

- "frmBestandsführung" vor den Namen der Steuerelemente wäre nur dann anzugeben, wenn sich die angesprochenen Steuerelemente auf einem anderen UserForm befinden. Da sie sich aber auf dem UF befinden in dem sich auch der Code befindet, kannst du den Zusatz weglassen. Wenn du ganz konsequent sein wolltest, dann kannst du jeweils Me. vor jedes Steuerelement setzen - das solltest du dann aber bei allen angesprochenen Steuerelementen machen und nicht nur bei einigen. Was die unbedingte Angabe von Me. betrifft, darüber streiten sich die Geister: einerseits ist es eigentlich nicht erforderlich, da sich der Code auf das UserForm mit dem Code bezieht - man schreibt ja auch nicht vor Range jedesmal ActiveSheet, wenn man im aktiven Tabellenblatt arbeitet. Andererseits wird gesagt, dass damit die eindeutige Zuordnung gewährleistet werden soll. Dann müsste man aber generell konsequent sein, und überall ActiveSheet.Range(...) schreiben bzw. in den Prozeduren wie Worksheet_Change müsste man jeweils auch Me. vor jede Zelladresse setzen. Wie gesagt - ist Ansichtssache und bei kleineren Projekten m.E. nicht unbedingt erforderlich, da die Zuordnung zum Steuerelement durch die Position des Codes gegeben ist.

- die Spaltenanzahl und die Spaltenbreiten der listBox soltest du direkt in den Eigenschaften der ListBox setzen, weil das bei jeder Neuzuweisung Zeit kostet

- anstelle den ComboBoxen die Inhalte mittels AddItem zuzuweisen, würde ich lieber alle relevanten Daten in das Hilfsblatt schreiben und den betreffenden Zellbereich in der Eigenschaft RowSource angeben - das erspart Zeit

- bei Zellangaben sollte man nur dann Buchstaben für die Spalte verwenden, wenn es sich um einen fixen Bereich handelt, also z.B. A2:F20. Sind dagegen Variablen im Spiel, benutzt man besser Zahlen anstelle der Buchstaben, weil Excel intern die Zellbereiche mit numerischer Angabe der Spalten ablegt, also in der Form Range(Cells(Zeile1, Spalte1), Cells(Zeile2, Spalte2)) oder Cells(Zeile, Spalte) - dadurch findet Excel die Spaltenangaben schneller


Bis später,
Karin

 

Hey :)

Zufälligerweise habe ich gestern sogar noch vor jedes Steuerelement Me. geschrieben und auch frmBestandsführung. damit ersetzt und vor jede Range Workbooks(WorkBookName).Worksheets("Bestand") geschrieben. WorkBookName habe ich oben als Public Variable deklariert und in der UserForm_Initialize definiert: WorkBookName = ActiveWorkbook.Name.

Es gab nämlich bei einer anderen Datei (mit 3 UserForms) Probleme, sobald noch eine zweite Datei geöffnet wurde und dann habe ich die Veränderungen dort einfach direkt mit in diese Datei übernommen.
Das hat auch eine ganze Weile gedauert :p


Die selektierten Daten (Suchen/Sortieren) sollen unbedingt bearbeitbar bleiben, also von der Funktionalität her so, wie es momentan ist.


Jetzt werde ich erst einmal deine Ratschläge bezüglich Spaltenanzahl und -breite und der ComboBoxen umsetzen.

Und danach lese ich noch einmal den letzten Punkt (Zellangaben) durch, versuche das irgendwie zu verstehen und ebenfalls umzuändern :p


Danke und liebe Grüße. :)

 

Hi,

wenn man sich auf die Mappe mit dem Code beziehen will, kann man ThisWorkbook verwenden: ThisWorkbook.Worksheets("Bestand") - man muss also den Mappennamen nicht erst auf eine Variable schreiben. Das habe ich im Code schon realisiert.

Für die erneute Aktivierung des gewählten Eintrags aus der ListBox benötigt man keine Variable - man kann dazu einfach die Tag-Eigenschaft eines (beliebigen) Steuerelements verwenden (in der Mappe die der ListBox), in die man den ListIndex schreibt. Diesen kann man dann wieder aus der Tag-Eigenschaft "zurückholen". Bei der Tag-Eigenschaft ist zu beachten, dass ihr Inhalt Text ist, weshalb man bei Zahlenwerten diesen erst in den geeigneten Typ umwandeln muss.

Ich habe den Code entsprechend angepasst, damit auch bei "gefilterter" Anzeige in der ListBox die korrekten Daten geändert werden. Dazu wird die relevante Zeile jetzt generell durch Suche des eindeutigen Suchkriteriums Barcode ermittelt und nicht durch den ListIndex, denn der stimmt ja nicht mit dem Zeilenindex des Tabellenblattes überein, wenn nur ausgewählte Inhalte angezeigt werden.
Nach Bearbeitung eines Eintrags wird die Filterung wieder hergestellt und der vorher ausgewählte Eintrag markiert.


Bei einigen Steuerelemente habe ich bereits Me. davorgeschrieben, aber nicht bei allem - du willst ja auch noch etwas zu tun haben... :D

Bis später,
Karin