Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1400to1404
1400to1404
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

UserForm - Format übernehmen + Einsortieren

Forumthread: UserForm - Format übernehmen + Einsortieren

UserForm - Format übernehmen + Einsortieren
13.01.2015 16:07:33
Christoph
Hallo zusammen,
ich habe eine etwas komplexere Datei für welche ich ersteinmal eine einfache UserForm erstellt habe, welche ich noch erweitern möchte. Bevor ich dies allerdings tue, möchte ich zwei Probleme ausräumen, an deren Lsg. ich bisher gescheitert bin.
Ich habe die betreffende Datei angehangen: https://www.herber.de/bbs/user/94972.xlsm
Durch klicken des Buttons 'Neues Projekt' öffnet sich die Eingabemaske. Wenn ich nun auf 'Neuer Eintrag' klicke möchte ich zwei Dinge, welche ich bisher nicht hinbekommen habe:
1.) Bei klicken des Buttons soll nicht nur die letzte Zeile angesteuert werden, sondern auch das Format und die Formeln der vorhergehenden Zeile übernommen werden.
2.) Wenn ich auf Speichern klicke, sollen mir alle einträge der Tabelle automatisch entsprechend des Startdatums (aufsteigend) in Spalte H sortiert werden.
Ich wäre wirklich dankbar für jeden Denkanstoß. Ich benötige keine komplette Lsg, sondern bin dankbar über jetweige Form der Hilfe.
Tausend Dank vorab und allen noch einen schönen Tag.
VG, Christoph

Anzeige

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: UserForm - Format übernehmen + Einsortieren
13.01.2015 16:26:07
Klaus
Hallo Christoph,
ohne deine Datei geöffnet zu haben, hier die Denkanstöße:
1)
die letzte Zelle ansteuern kannst du?
activecell.entirerow.copy
activecell.offset(1,0).entirerow.pastespecial xlpastevalues
kopiert dir die Formate in eine Zelle drunter.
2)
Makrorekorder an, den Sortiervorgang ausführen, Makrorekorder aus. Das Makro anpassen (letzte Zeile und so weiter) und in das

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

reinkopieren.
Grüße,
Klaus M.vdT.

Anzeige
AW: UserForm - Format übernehmen + Einsortieren
13.01.2015 17:47:04
Christoph
Hallo Klaus,
ich bekomme es leider nicht hin, deinen Befehl in mein vorhandenes Makro für einen neuen Eintrag zu integrieren. Mein Makro steuert zwar die letzte Zeile an, allerdings werden die Formate und Formeln der vorherigen halt nicht übernommen und ich kann deinen Befehl nicht integrieren. Stehe wirklich noch ganz am Anfang meiner 'VBA-Karriere'.
Private Sub CommandButton1_Click()
Dim lZeile As Long
lZeile = 6
Do While Trim(CStr(Tabelle1.Cells(lZeile, 1).Value))  ""
lZeile = lZeile + 1
Loop
Tabelle1.Cells(lZeile, 1) = CStr("Neuer Eintrag Zeile " & lZeile)
ListBox1.AddItem CStr("Neuer Eintrag Zeile " & lZeile)
ListBox1.ListIndex = ListBox1.ListCount - 1
End Sub

Wahrscheinlich müsste das Makro für diesen Zweck anders aussehen, oder? Ich bringe mir VBA gerade selber mit Hilfe eines Lehrbuches bei. Es ist manchmal gar nicht so leicht die Inhalte seinen wünschen anzupassen.
Tausend Dank schonmal für deine Hilfe.
VG, Christoph

Anzeige
AW: UserForm - Format übernehmen + Einsortieren
14.01.2015 08:18:58
Klaus
Hi,
das (langsame) letzte-Zeile ermitteln per Schleife muss in Excel nicht sein. Geh mal manuell in die letzte Zelle, A1048576, und drücke STRG+UP. Du landest in der letzten befüllten Zeile aus Spalte A! Das geht auch per Makro, statt STRG+UP ist der befehl dort aber end(xlup). Schaus dir mal im Makro an.
Auf die Offset-Geschichten habe ich verzichtet, um das Makro einfacher verständlich zu halten. 
Private Sub CommandButton1_Click()
Dim lZeile As Long
With Tabelle1
lZeile = .Cells(.Rows.Count, 1).End(xlUp).Row 'letzte Zeile bestimmen
.Cells(lZeile, 1).EntireRow.Copy 'ganze Zeile kopieren
.Cells(lZeile + 1, 1).EntireRow.PasteSpecial xlPasteFormats 'ab Spalte A einfügen, aber nur  _
Formate
Application.CutCopyMode = False 'Kopierameisen abschalten
.Cells(lZeile + 1, 1) = CStr("Neuer Eintrag Zeile " & lZeile)
ListBox1.AddItem CStr("Neuer Eintrag Zeile " & lZeile)
ListBox1.ListIndex = ListBox1.ListCount - 1
End With
End Sub
Grüße,
Klaus M.vdT.

Anzeige
AW: UserForm - Format übernehmen + Einsortieren
14.01.2015 14:07:40
Christoph
Hallo Klaus,
vielen Dank für Deine Antwort. Du hast mir sehr geholfen und ich bin ein schritt weitergekommen - habe den Befehl etwas erweitern müssen.
Zwei Probleme bestehen allerdings noch:
1.) in der jetzigen Form werden auch die Inhalte der vorherigen Zeile mit übernommen - ich möchte allerdings nur das Format und die Formel übernehmen. Ich weiß, dass dies über den Befehl '.SpecialCells(xlCellTypeConstants).ClearContents' geht. Wie kann ich diesen in mein bestehendes Makro einbauen?
Private Sub CommandButton1_Click()
Dim lZeile As Long
With Tabelle1
lZeile = .Cells(.Rows.Count, 1).End(xlUp).Row 'letzte Zeile bestimmen
.Cells(lZeile, 1).EntireRow.Copy 'ganze Zeile kopieren
.Cells(lZeile, 1).Insert xlDown
.Cells(lZeile + 1, 1).EntireRow.PasteSpecial xlPasteFormats
.Cells(lZeile + 1, 1).EntireRow.PasteSpecial xlPasteFormulas
Application.CutCopyMode = False 'Kopierameisen abschalten
.Cells(lZeile + 1, 1) = CStr("Neuer Eintrag Zeile " & lZeile)
ListBox1.AddItem CStr("Neuer Eintrag Zeile " & lZeile)
ListBox1.ListIndex = ListBox1.ListCount - 1
End With
End Sub

2.) Ich hänge immer noch bei der Sortierfunktion, da ich diese in mein Makro zum speichern eines neuen Eintrages integrieren möchte. Mit dem Speichern eines Eintrages soll die gesamte Liste neu sortiert werden nach einem Startdatum, welches in Spalte I befindlich ist.
Private Sub CommandButton4_Click()
Dim lZeile As Long
If ListBox1.ListIndex = -1 Then Exit Sub
If Trim(CStr(TextBox1.Text)) = "" Then
MsgBox "Sie müssen mindestens einen Namen eingeben!", vbCritical + vbOKOnly, "FEHLER!"
Exit Sub
End If
lZeile = 6
Do While Trim(CStr(Tabelle1.Cells(lZeile, 1).Value))  ""
If ListBox1.Text = Trim(CStr(Tabelle1.Cells(lZeile, 1).Value)) Then
Tabelle1.Cells(lZeile, 1).Value = Trim(CStr(TextBox1.Text))
Tabelle1.Cells(lZeile, 25).Value = TextBox2.Text
Tabelle1.Cells(lZeile, 26).Value = TextBox3.Text
Tabelle1.Cells(lZeile, 27).Value = TextBox4.Text
If ListBox1.Text  Trim(CStr(TextBox1.Text)) Then
Call UserForm_Initialize
If ListBox1.ListCount > 0 Then ListBox1.ListIndex = 0
End If
Exit Do
End If
lZeile = lZeile + 1
Loop
End Sub

Anzeige
Teilantwort: Konstanten löschen, formeln behalten
15.01.2015 12:10:23
Klaus
Hallo Christoph,
Da du sehr viele Fragen hast, mache ich sehr viele einzelne Themenzweige auf, je nachdem welche Teilantwort ich gerade habe - sonst verliere ich selbst den Überblick :-) Je nachdem wie komplex die Antwort ist, poste ich nur Makrocode oder lade die Datei hoch.
Los geht's:
1.) in der jetzigen Form werden auch die Inhalte der vorherigen Zeile mit übernommen - ich möchte allerdings nur das Format und die Formel übernehmen. Ich weiß, dass dies über den Befehl '.SpecialCells(xlCellTypeConstants).ClearContents' geht. Wie kann ich diesen in mein bestehendes Makro einbauen?
Antwort:
Dim lZeile As Long
With Tabelle1
lZeile = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lZeile, 1).EntireRow.Copy
.Cells(lZeile, 1).Insert xlDown
.Cells(lZeile + 1, 1).EntireRow.PasteSpecial xlPasteFormats
.Cells(lZeile + 1, 1).EntireRow.PasteSpecial xlPasteFormulas
.Cells(lZeile + 1, 1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
Application.CutCopyMode = False
.Cells(lZeile + 1, 1) = CStr("Neuer Eintrag Zeile " & lZeile)
ListBox1.AddItem CStr("Neuer Eintrag Zeile " & lZeile)
ListBox1.ListIndex = ListBox1.ListCount - 1
End With
Grüße,
Klaus M.vdT.

Anzeige
Teilantwort: Kritik an der Namensgebung
15.01.2015 12:21:51
Klaus
Hi,
deine Namensgebung ist ja furchtbar! Wenn du einen Button hast der einen neuen Beitrag einfügt, dann nenne den nicht "CommandButton1" - sondern nenn den um in "Cmb_BeitragNeu". Das gleiche gilt für alle anderen Objekte - nenne die ListBox nicht "ListBox1", sondern "ListBox_VorhandeneEintraege". Solange du nur ein oder zwei Buttons hast, ist das nur unnötiger Mehraufwand - aber wenn das Projekt wächst (und deins ist ja schon relativ groß) erschwerst du dir das debuggen unnötig.
Beispiel: ich bin grad in
Private Sub CommandButton4_Click()
was genau macht der nochmal? Hättest du den gleich in "Cmb_Speichern" umbenannt, siehst du beim coden gleich
Private Sub Cmb_Speichern_Click()
und weisst genau was du gerade machst. Gleiches für die Listbox: was exakt macht denn die Zeile
If ListBox1.ListIndex = -1 Then Exit Sub da?
Mit guter Namensvergabe sehe und verstehe ich sofort:
If ListBox_VorhandeneEintraege.ListIndex = -1 Then Exit Sub
aha! Wenn kein Eintrag gewählt wurde, dann brich das Makro ab. NOCH scheint das pedantisch zu sein - aber was machst du, wenn dein nächstes Projekt mal 27 verschiedene Listboxen hat? Dann verlierst du komplett den Überblick!
Das "Exit Sub" ein schlimmes Kommando ist dass ich aus Prinzip vermeiden würde, hier nur als Einschub.
Ich leb jetzt damit und benenne nichts um - die Fleißarbeit lasse ich dir. Aber mach sie dir und gewöhne sie dir an, als gutgemeinter Rat. Stell dir mal vor die Datei wird 3 Jahre benutzt, und dann wirst du zum debuggen geholt - erinnerst du dich dann noch daran, was ListBox1 jetzt war?
Achso, KOMMENTARE. Kommentare in den Code. Viele. Das hilft ungemein beim Nachvollziehen. Mindestens eine Klartextbeschreibung am Anfang jedes Codeteils, zum Beispiel:
'Button speichert bei korrekter Auswahl eines Eintrages, sortiert vorher nach Kalenderwochen
So weisst du gleich im Klartext was dein Code machen soll und musst dich weniger reinlesen.
LG,
Klaus

Anzeige
Teilantwort: Sortiere_Before_Save
15.01.2015 12:32:49
Klaus
Hi,
ich habe die Sortierroutine extern geschrieben (kopiere sie einfach in Modul 1). Innerhalb deines CommandButton4 kannst du sie einfach aufrufen, wo es dir passt - ich denke mal, kurz vor EndSub. Den Teil zwischen DO und LOOP verstehe ich nicht, darum fasse ich ihn nicht an.
Tabellennamen und eventuell ein paar RANGE-Anweisungen musst du natürlich anpassen (oder heisst deine Tabelle wirklich Tabelle1?)
Sub SortiereKW()
'### sortiere Tabelle1 nach Kalenderwochen, bevor gespeichert wird
Dim letzteZeile As Long
Const ersteZeile As Long = 5      'Überschriften in Zeile 5
Const sortiereSpalte As Long = 9  'nach Spalte 9 = I sortieren
With Worksheets("Tabelle1")
letzteZeile = .Cells(.Rows.Count, sortiereSpalte).End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range(.Cells(ersteZeile, sortiereSpalte), .Cells(letzteZeile, _
sortiereSpalte)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Sort.SetRange .Range("A" & ersteZeile & ":AZ" & letzteZeile)
.Sort.Header = xlYes
.Sort.MatchCase = False
.Sort.Orientation = xlTopToBottom
.Sort.SortMethod = xlPinYin
.Sort.Apply
End With
End Sub
LG,
Klaus
(was macht eigentlich das riesige grüne Rechteck in BC20 bis BP50?)

Anzeige
AW: UserForm - Format übernehmen + Einsortieren
14.01.2015 14:10:10
Christoph
Hallo Klaus,
entschuldige, ich habe den Beitrag zu früh abgesendet. Ich wollte noch fragen, ob du mir auch bei diesem letzten Schritt noch behilfich sein könntest. Ich gelobe Besserung meiner Fähigkeiten und arbeite weiter daran.
Ich wünsche Dir einen schönen Tag.
VG, Christoph

Anzeige
AW: UserForm - Format übernehmen + Einsortieren
14.01.2015 15:45:25
Klaus
Hallo Christoph,
sieht beides einfach aus, ich bin mir sicher das schaffe ich was. Aber nicht mehr heute :-) Morgen gegen Mittag komme ich dazu.
Da es jetzt etwas eingemachter an den Code geht, magst du mir bitte eine Musterdatei hochladen die Spalten/Zeilen/Tabellenmäßig genau deinem Master entspricht. Dann muss ich keinen ungetesteten Code hochladen.
Grüße,
Klaus M.vdT.

Anzeige
AW: UserForm - Format übernehmen + Einsortieren
14.01.2015 16:07:40
Christoph
Hey Klaus,
vielen Dank dafür... Ich werde die Datei morgenfrüh dann hochladen. Einen schönen Abend Dir.
VG, Christoph

AW: UserForm - Format übernehmen + Einsortieren
15.01.2015 12:03:02
Christoph
Hallo Klaus,
in der Zwischenzeit habe ich noch etwas an meiner Tabelle gearbeitet. Ich habe Dir eine Datei angehangen. Es bestehen ggw. noch ein paar Probleme, an deren Behebung ich weiter arbeite - es ist allerdings für mich nicht so einfach, da ich ggw. autodidaktisch unterwegs bin.
https://www.herber.de/bbs/user/95020.xlsm
1.) Mit den in der Tabelle bestehenden Einträge funktioniert meine User-Form recht gut - in der UserForm ist ein Makro hinterlegt, welches mir bei Eingabe einer ID (In der Tabelle sind nur die ID's 111, 222, 333 bis 999 hinterlegt)direkt den betreffenden Ort angezeigt. Ich kann die bestehenden Einträge ändern und speichern der auch löschen.
Anders sieht es aus, wenn ich einen neuen Eintrag anlege. Ich gebe die ID ein, es wird der betreffende Ort ermittelt, weiterhin fülle ich die anderen Felder aus und dann funktioniert leider weder das Speichern noch das Schließen der Datei. Ich glaube ich bin dem Problem auf der Spur, weiß aber gerade nicht, wie ich die Sache anders lösen kann.
2.) Es wäre schön, wenn mit dem Betätigen des Speicherbuttons, automatisch ein Neu-Einsortierung des betreffenden Eintrags in die Liste erfolgt (Sortierung nach Startdatum), insofern sich das Datum geändert hat.
3.) Wenn eine ID eingegeben wird, welche nicht in der Quelldatei (Standorte) hinterlegt ist, dann bricht das Makro ab - schöner wäre es natürlich, wenn die Aussage kommt, dass die ID falsch ist oder dergleichen.
Ich wäre Dir sehr sehr dankbar, wenn du mir ein paar hilfreiche Denkanstöße geben könntest - um Gottes Willen brauche ich keine komplette Lösung, sondern fuchse mich weiter in die Sache - natürlich würde ich aber auch nicht Nein sagen ;-).
Ich freue mich auf deine Antwort und wünsche Dir erstmal einen schönen Tag.
VG, Christoph

Anzeige
Teilantwort: Makroabbruch bei ungültiger ID
15.01.2015 12:43:17
Klaus
Hallo,
wenn eine Formel zu #NV führt, bricht VBA ab. Das ist so. Umgehen kannst du das nur mit einer On-Error Anweisung, mit ein paar unschönen GOTO kannst du dann ein paar Codezeilen einfügen die nur im Fehlerfall greifen.
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo hell 'Abbruch durch Formelfehler EVALUATE abfangen
TextBox6.Text = Evaluate("=VLOOKUP(" & TextBox1.Text & ",'Standorte'!A2:S355,5,0)")
CommandButton4.Enabled = True 'Es ist möglich, zu speichern - denn die ID ist gültig
GoTo heaven: 'Fehlerbehandlung überspringen, falls kein Fehler vorkam
hell:
'Fehlerbehandlung: User aufmerksam machen, Info-Texte in die Textboxen schreiben
MsgBox ("Die ID-Nummer " & TextBox1.Text & " gibt es nicht!")
TextBox6.Text = "SPEICHERN NICHT MÖGLICH"
TextBox1.Text = "ungültig"
CommandButton4.Enabled = False 'speichern verhindern durch deaktivieren des Buttons
'bei eingabe einer gültigen ID-Nummer wird der speichern-Button wieder aktiviert
heaven:
End Sub
Jedoch halte ich das für ein Musterbeispiel von schlampiger Programmierung :-) Warum darf der User überhaupt eine falsche Nummer in die Textbox1 eingeben? Mach doch statt der TextBox1 lieber eine Listbox oder Combobox aus der eine gültige Nummer ausgewählt werden kann. Das ist komfortabler für den User, wirkt eleganter und die aufwendige Fehlerbehandlung kann wegfallen.
LG,
Klaus

Anzeige
AW: Teilantwort: Makroabbruch bei ungültiger ID
15.01.2015 14:08:44
Christoph
Hallo Klaus,
vielen Dank dennoch für deine Hilfe. Ich kann nicht beurteilen ob das Bsp. von minderer Qualität ist oder nicht, aber vertraue Dir da natürlich total. Ich arbeite mit einem Lehrbuch und habe versucht das darin enthaltene Bsp. so gut es geht an meinen Sachverhalt anzupassen - das hat mich schon alleine einen Haufen arbeit gekostet. Klappt wohl nicht... :-) Glaube ich lasse das Ganze mal ruhen und starte vielleicht doch mit einem Kurs, auf dem ich dann mein Wissen aufbauen und erweitern kann. Hast du vielleicht dennoch einen Buchtipp?
Wünsche Dir noch einen schönen Tag und nochmals Danke.
VG, Christoph

Anzeige
AW: Teilantwort: Makroabbruch bei ungültiger ID
15.01.2015 14:31:03
Klaus
Hallo Christoph,
Glaube ich lasse das Ganze mal ruhen
Was das Gegenteil von dem ist was ich erreichen wollte :-/
Hast du vielleicht dennoch einen Buchtipp?
www.herber.de/forum ist besser als jedes Buch!
Grüße,
Klaus M.vdT.

Teilantwort: speichern bei neuer ID
15.01.2015 12:56:37
Klaus
Hi,
dein Makro durchsucht Spalte A bis es einen Treffer mit dem Listboxeintrag erhält..
     lZeile = 6
Do While Trim(CStr(Tabelle1.Cells(lZeile, 1).Value))  ""
If ListBox1.Text = Trim(CStr(Tabelle1.Cells(lZeile, 2).Value) & ", t " & CStr(Tabelle1. _
Cells(lZeile, 1).Value)) Then

davon abgesehen dass ich dieses DO WHILE total daneben finde und das völlig anders lösen würde ... es kann keinen Treffer finden. Der neue Eintrag heisst ja "Neuer Eintrag Zeile 17", in der Listbox steht aber bereits ", t Neuer Eintrag Zeile 17".
Ich geh jetzt nicht forschen, wo das ", t " herkommt! Bei der Speichern-Teilantwort steige ich aus, sorry. Du musst deinen Code durchforsten, warum wo was in diese Zelle eingetragen wird und warum das anders als in der ListBox ist - aufgrund des nicht kommentierten Codes und der nicht-sprechenden Bezeichnungen (ich kritisierte es vorhin schon) ist mir das zu viel, ich finde mich nicht zurecht.
Grüße,
Klaus M.vdT.
Anzeige
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige