Frage von xXSnip3rXx, 167

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

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Suboptimierer, 93

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
Kommentar von xXSnip3rXx ,

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

Kommentar von xXSnip3rXx ,

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

Kommentar von Suboptimierer ,

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.

Kommentar von Ninombre ,

Ü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

Kommentar von xXSnip3rXx ,

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

Kommentar von Suboptimierer ,

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.

Kommentar von xXSnip3rXx ,

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 ...?

Kommentar von Suboptimierer ,

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.

Kommentar von xXSnip3rXx ,

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

Ich schaue mal was rauskommt ^^

Kommentar von xXSnip3rXx ,

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 ...

Kommentar von Suboptimierer ,

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.

Kommentar von xXSnip3rXx ,

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...

Kommentar von Suboptimierer ,

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.

Antwort
von xXSnip3rXx, 74

Vielen Dank euch beiden :) Schnurrt wie eine Kleine Katze ;)


... kann leider nur einem von euch die "Hilfreichste antwort" geben ;) [3 Stunden to go ^.^]

Aber @Ninombre fühl dich mit Belobigt ;D

MfG Tim




Kommentar von Ninombre ,

Suboptimierer hat hier klar den wesentlichen Beitrag geleistet, insofern ist das absolut ok. Hab ja nicht mal eine eigene Antwort geschrieben ;-)

Kommentar von xXSnip3rXx ,

Aber genau zu deinem kleinen beitrag habe ich eine frage ^^

Wie wo soll ich das genau einfügen .. bei der Change Funktion löscht er ja immer wieder mit der rows(i).delete nach jeder änderung der Lfdnr die spalte zu dieser ... wie kann ich das ändern? Suboptimierer versucht es mir oben zu erklären aber ich verstehe das nicht ganz .. :D dachte zuerst ich habe es verstanden .. war aber nichts :/

Kommentar von Ninombre ,

Wie ist denn der vollständige Code, der bei der Change Funktion abläuft? Du liest die Daten zu einer vorhandenen lfd. Nummer ein - dann muss das Löschen erfolgen und danach das Schreiben. Genau kann ich die Stelle nicht benennen ohne Code.

Wie Suboptimierer allerdings auch schon sagte besteht bei dieser Variante das Risiko, dass der User zwar die vorhanden Daten lädt (und der Datensatz gelöscht wird) aber nicht die gänderten Daten speichert. Dann ist der Satz einfach weg.

Die mit ' kommentierten Zeilen gehören aus meiner Sicht raus:

mit dem Countif prüfen, ob es die lfd. Nummer schon gibt -> wenn 0 (es also nicht gefunden wird) Ermittlung über die letzte Zeile, sonst die If-Schleife solange bis die aktuelle Zeile in Spalte A -> cells(i,1) = lfd-Nummer aus der Userform ist. ELZ = i (damit ist ELZ die Zeile, die überschrieben werden soll) und die For-Schleife beenden.

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

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
Kommentar von xXSnip3rXx ,

Hi,

ich habe dir mal meine Datei bei dropbox hochgeladen .. der VBA code ist zu lang um ihn hier zu posten :/

https://www.dropbox.com/s/s1jau44by32c5re/%C3%9Cbersicht%20Test%20Datei%20f%C3%B...

MfG

Kommentar von xXSnip3rXx ,

die Datei ist halt nur ohne Stammdaten ^.^ Datenschutz

Kommentar von Ninombre ,

Und mit Passwort... 

Kommentar von xXSnip3rXx ,

sry :D musste langezeit was anderes machen .. pw wäre Ausbildung jedoch habe ich es jetzt schon hinbekommen .. danke tz. euch allen ;)

Wenn du interesse an der datei hast wie sie aktuell ist kann ich sie dir gerne mal zu kommen lassen ^^

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten