Wie kann ich in VBA-Excel per laufender Nummer meine Userform Einlesen und danach diese "Einlesung" bearbeiten?

Hi, ich arbeite momentan an einer Userform(Bild) womit ich in meiner Tabelle "Gesamtdaten": In die erste leere Zeile einen neuen Mitarbeiter anlegen kann.

Diese läuft auch einwandfrei.

Der Übernehmen Button hat folgende Programierung: (ist nur der teil den Ihr auch auf dem Screenshot seht ... der rest wäre zu viel :D )

Private Sub cmdÜbernehmen_Click()

Dim ELZ As Long

ELZ = Sheets("Gesamtdaten").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Gesamtdaten").Cells(ELZ, 1).Value = Me.TXTlfdNr.Value
Sheets("Gesamtdaten").Cells(ELZ, 2).Value = Me.TXTLehrjahr.Value
Sheets("Gesamtdaten").Cells(ELZ, 3).Value = Me.CBAusbildungsart.Value
Sheets("Gesamtdaten").Cells(ELZ, 4).Value = Me.CBAusbilder.Value
Sheets("Gesamtdaten").Cells(ELZ, 5).Value = Me.TXTName.Value
Sheets("Gesamtdaten").Cells(ELZ, 7).Value = Me.TXTVorname.Value
Sheets("Gesamtdaten").Cells(ELZ, 8).Value = Me.TXTPersonalnummer.Value
Sheets("Gesamtdaten").Cells(ELZ, 9).Value = Me.CBGeschlecht.Value
Sheets("Gesamtdaten").Cells(ELZ, 10).Value = Me.TXTStrasseHsNr.Value
Sheets("Gesamtdaten").Cells(ELZ, 11).Value = Me.CBPLZ.Value
Sheets("Gesamtdaten").Cells(ELZ, 12).Value = Me.CBOrt.Value

Der Abbruch button:

Private Sub cmdAbbruch_Click()

Unload Me

End Sub

Nun würde ich jedoch noch gerne einen button haben der wenn ich oben im Textfeld "lfdNr" eine schon vorhandene nummer eingebe. Dieser sofort alle daten in die jeweilge Combbox/Textbox einträgt und falls ich eine änderung vornehme diese auch mit rückfrage (msgbox Datensatz wurde geänder etc.) auch wieder in die Zeile der passenden LfdNr übernimmt.

Ich bin recht neu in VBA ... bzw. arbeite diese Jahr das 1. mal überhaupt mit excel ... desswegen hole ich mir (wenn ich nach [nun fast 1 woche] suchen keine lösung zu diesem problem finde die mir "zusagt" bzw. ich diese verstehe ... ( will ja was lernen ;'D )

Ich entschuldige mich schonmal für mein etwas "konfuses" auftreten aber hoffe auf Unterstützung :)

Was villeicht auch noch zu erwähnen ist:

Die lfdNr setzt sich automatisch beim öffnen der Userform auf die LfdNr die ein Neuer Datensatz automatisch bekommen sollte.

    TXTlfdNr = Sheets("DropDown-Menü's").Range("J15") 'In J15 steht die Formel =ANZAHL2(Gesamtdaten!A2:A502)

MfG Tim

Answer 1

[Suboptimierer]

Ich würde eine neue Funktion, bzw. ein Change-Ereignis an dem Eingabefeld für die Laufende Nummer.

Private Sub TXTlfdNr_Change()
  Dim i

  For i = 2 To Sheets("DropDown-Menü's").Range("J15").Value + 1
    If Me.TXTlfdNr.Value = CStr(Sheets("Gesamtdaten").Cells(i, 1).Value) Then
      Me.TXTLehrjahr.Value = Sheets("Gesamtdaten").Cells(i, 2).Value
      Me.CBAusbildungsart.Value = Sheets("Gesamtdaten").Cells(i, 3).Value
      Me.CBAusbilder.Value = Sheets("Gesamtdaten").Cells(i, 4).Value
      '...
    End If
  Next
End Sub

Comments

[NekoIronyOfFate]

Danke schonmal für die schnelle Antwort .. werde ich gegen 15 Uhr mal ausprobieren und dann Bescheid geben

[NekoIronyOfFate]

Vielen Dank schon mal für diesen Code :) 

habe ihn nun bei mir eingepflegt ... mit ner kleinen Änderung:

Private Sub CBlfdNr_Change()
 'Ist jetzt ein DropDownmenü mit allen momentanen LfdNr.
  Dim i

  For i = 2 To Sheets("DropDown-Menü's").Range("J15").Value + 1

    If Me.CBlfdNr.Value = CStr(Sheets("Gesamtdaten").Cells(i, 1).Value) Then


        Me.TXTLehrjahr.Value = Sheets("Gesamtdaten").Cells(i, 2).Value

        Me.CBAusbildungsart.Value = Sheets("Gesamtdaten").Cells(i, 3).Value

        Me.CBAusbilder.Value = Sheets("Gesamtdaten").Cells(i, 4).Value

Er liest perfekt ein :) jedoch Überschreibt bzw. bearbeitet nicht die aktuelle auswahl :/ : wenn ich auf "Übernehmen" klicke:

- Setzt er mir den Datensatz unten neu rein .. also er bearbeitet ihn nicht sondern behandelt ihn wie einen neuen Datensatz .. was müsste ich dafür noch ändern?

Ps: Was mir jetzt vorhin auch noch einfiel ... geht das auch das ich per Button "Löschen" den ausgewählten datensatz lösche?

MfG Tim

[Suboptimierer]

@NekoIronyOfFate

Beim Schreiben musst du auf ähnlicher Art und Weise erstmal schauen, ob du die ID findest. Sonst steht i am Ende der Schleife automatisch auf der nächsten, freien Zeile und du brauchst nur mittels Cells(i,... die neue Zeile beschreiben.

[Ninombre]

@NekoIronyOfFate

Überschreiben ist vermutlich "schöner", wenn die laufende Nummer in der Reihenfolge bleiben soll. Ansonsten ist es
for i=1 to .... (wie Du schon die Spalte durchsuchst)
if cells(i,1)= (ebenfalls wie von Dir vorgesehen)
rows(i).delete
exit for
end if

Rufst Du nach dem CBlfdNr_Change dann die andere Sub auf? Dann könnte man beim Aufruf einen Parameter mitgeben, ob das ELZ anhand der letzten Zeile ermittelt werden soll oder eine vorhandene lfd. Nummer überschreibt.

Alternativ: In Deinem ersten Code die Bestimmung des ELZ etwas erweitern:

If WorksheetFunction.CountIf(Range((Sheets("Gesamtdaten").Cells(1, 1).Value), Cells(Sheets("Gesamtdaten").Cells(Sheets("Gesamtdaten").Cells(Rows.Count, 1).End(xlUp).Row, 1).Value)), Me.TXTlfdNr.Value) = 0 Then ELZ = Sheets("Gesamtdaten").Cells(Rows.Count, 1).End(xlUp).Row + 1
Else:
For i = 1 To Sheets("Gesamtdaten").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("Gesamtdaten").Cells(i, 1).Value = Me.TXTlfdNr.Value Then
ELZ = i
Exit For
End If
Next i

Hinweis: Ich hab direkt Deine Bezeichnungen für Sheets und Userform genommen beim Zusammenstellen - damit läuft es bei mir natürlich nicht und könnte noch ein paar Fehlermeldungen ausspucken

[NekoIronyOfFate]

@Ninombre

Schonmal danke an euch zwei das ihr mir helfen möchtet :)

Nun Ich kenne mich fast nicht mit VBA aus ^^ ich habe die Userform mithilfe von verschiedenen TuT Viedeos und Foren beiträgen gefertigt / abgeändert vereinfacht ...

Könnte ich euch eine "abgeänderte" ( Datenschutz ^.^) Datei hochladen sodass ihr mir dort einen Teil Vormacht den ich dann weiterführen könnte ... ( am besten mit Erklärung :) )

Ich wüsste jetzt z.b. nicht was ich mit deinem Code richtig machen sollte @Ninombre ... wo soll ich ihn einfügen..:

Private Sub cmdÜbernehmen_Click()

Dim ELZ As Long

    ELZ = Sheets("Gesamtdaten").Cells(Rows.Count, 1).End(xlUp).Row + 1
    If WorksheetFunction.CountIf(Range((Sheets("Gesamtdaten").Cells(1, 1).Value), Cells(Sheets("Gesamtdaten").Cells(Sheets("Gesamtdaten").Cells(Rows.Count, 1).End(xlUp).Row, 1).Value)), Me.TXTlfdNr.Value) = 0 Then ELZ = Sheets("Gesamtdaten").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Else: ' Fehler "Else ohne If"

    For i = 1 To Sheets("Gesamtdaten").Cells(Rows.Count, 1).End(xlUp).Row
    If Sheets("Gesamtdaten").Cells(i, 1).Value = Me.TXTlfdNr.Value Then
    ELZ = i
    Exit For
    End If
    Next i
    If Sheets("Gesamtdaten").Cells(i, 2).Value = Me.TXTLehrjahr.Value Then
    ELZ = i
    Exit For
    End If
    Next i

Dort? Da komm ein fehler raus .. :/

Würde euch das hochladen der Datei was helfen .. mir zu helfen?

MfG Tim

[Suboptimierer]

@NekoIronyOfFate

Ninombre hat im Wesentlichen zwei Konzepte vorgeschlagen.

1) Statt die Zeile zu überschreiben, kannst du einfach die alte Zeile löschen (am Ende des Change-Ereignisses) und dann ganz normal, egal ob es die ID schon vorher gab oder nicht, die Eingabe als neuen Datensatz unten anhängen

2) Wenn du im Change-Ereignis der ID dir die Zeile der ID merken würdest, könntest du sie einfach beim Speichern wiederverwerten. Aber Achtung! Du musst Konsistenz gewährleisten, sollte die ID abgeändert werden.

[NekoIronyOfFate]

@Suboptimierer

1) und woher weiß VBA welche Zeile es löschen soll?

2) Bei dem Code kommt der Fehler "Else ohne If" .. wie kann ich den Lösen ...?

[Suboptimierer]

@NekoIronyOfFate

1) VBA findet die zu löschende Zeile wie im Change-Ereignis, welches ich gepostet habe. Nachdem du alle Werte ins Formular übernommen hast, gibst du den von Ninombre vorgeschlagenen Befehl ein: rows(i).delete

2) Verstehe Ninombres Code eher als Pseudocode. Das : hinter dem Else irritiert mich zum Beispiel. Hinter dem Then gehört ein Zeilenumbruch oder das Else muss in dieselbe Zeile wie das If. Aber auch lässt sie die Schleife bei 1 anfangen zu zählen.

Es sollte nur das Prinzip veranschaulicht werden.

[NekoIronyOfFate]

@Suboptimierer

Achsoo Danke ... jetzt habe ich es verstanden ^^

Ich schaue mal was rauskommt ^^

[NekoIronyOfFate]

@NekoIronyOfFate

Obwohl .. ich habe jetzt doch ein größeres Problem .. (zum glück in der Test datei aufgefallen..)

Wo muss ich den befehl:
rows(i).delete jetzt hintuhen .. wenn ich ihn (irgenwie auch logisch -.-') ins change ereignis kopiere löscht er mir bei jder änderung der lfdnr die entsprechende zeile ... müsste ich einen vollkommen neuen Private Sub machen? Da wenn ich den befehl in den "ÜBernehmen" button hinzufüge, kommt nur ein error ...

[Suboptimierer]

@NekoIronyOfFate

Der Löschbefehl muss natürlich innerhalb von 

If Me.CBlfdNr.Value = CStr(Sheets("Gesamtdaten").Cells(i, 1).Value) Then

stehen und zwar als letzte Zeile vor dem End If.

Versuch den Quellcode mal zu verstehen. Es darf nur dann eine ID gelöscht werden, wenn sie in der Liste gefunden wird.

Risiko ist, dass die Änderung verloren geht, wenn zwischen Change-Ereignis und Einfügen des neuen Datensatzes das Programm abstürzt. Wird die Maske ohne zu speichern geschlossen, geht der Datensatz ebenfalls verloren.

[NekoIronyOfFate]

@Suboptimierer

Ich habe den befehl in der letzten spalte von diesem Befehel vor dem End If gesetzt ... aber dann löscht er bei jedem ändern der ID den gesamten datensatz ...

Ist das das "Risiko" .. wenn ja .. wie kann ich das umgehen...

[Suboptimierer]

@NekoIronyOfFate

Dadurch entsteht ein Risiko. Die Idee ist folgendermaßen:

1. Such die ID in der Liste
2. bei Fund: Übertrage den Datensatz ins Formular
3. Lösche den Datensatz in der Liste
4. Pass den Datensatz im Formular an
5. Schreibe den Datensatz als neuen Datensatz weg.

Du kannst das Problem umgehen, immer manuell speichern zu müssen, indem du Vor jedem Wechsel der ID (BeforeUpdate) den aktuellen Stand speicherst.

____________

Falls das zu kompliziert werden sollte, könntest du immer noch auf die Idee zurück fallen, keine Löschung vorzunehmen, sondern einfach beim Change der ID nach der ID suchen und die Formulardaten füllen und beim Speichern wieder suchen, bei Fund die Zeile aktualisieren, ansonsten eine neue hinten dran hängen.