VBA in Excel - Prozedur wird nicht ausgeführt

axel_o · 10. November 2019 um 00:55

Hallo,

ich habe ein Formular, aus welchem die Werte 1 bis 5 ausgelesen werden. Im ersten Schritt sollen die Werte dann an das Ende einer Tabelle geschrieben werden, im zweiten schritt soll die Tabelle nach den Werten der Spalte 1 und dann in zweiter Ordnung nach den Werten der Spalte 2 sortiert werden.

Das ganze läuft durch den nachfolgenden Code ab. Dieser funktioniert auch, wenn ich das Formular aus der Entwicklungsumgebung starte. Es funktioniert jedoch nicht, wenn das Formular über ein Makro „Auto_Open“ starte. Dann werden zwar die Daten richtig in die Tabelle geschrieben, die Sortierung findet jedoch nicht statt.

Den Code für die Sortierung habe ich als Makro aufgezeichnet und abgeändert um die letzte Zeile für den Sortierbereich zu definieren.

Ich habe auch schon probiert, die Sortierung als extra Prozedur zu starten, jedoch klappt dann das auffinden der letzten Zeile (loLetzte) nicht mehr. Es wird dann eine Fehlermeldung generiert.

Vielen Dank für Hilfe

Axel

Sub Start()
Load usrDatenEingabe
usrDatenEingabe.Show
End Sub

Private Sub cmdDatenSchreiben_Click()

Dim Spalte As Integer, loLetzte As Long

With ThisWorkbook.Worksheets(„Eingaben“)
loLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
Spalte = 1
.Cells(loLetzte + 1, Spalte) = txtWert1.value
Spalte = Spalte + 1
.Cells(loLetzte + 1, Spalte) = txtWert2.value
Spalte = Spalte + 1
.Cells(loLetzte + 1, Spalte) = txtWert3.value
Spalte = Spalte + 1
.Cells(loLetzte + 1, Spalte) = txtWert4.value
Spalte = Spalte + 1
.Cells(loLetzte + 1, Spalte) = txtWert5.value
End With

Range(Cells(1, 1), Cells(loLetzte + 1, 5)).Select
Selection.Sort _
Key1:=Cells(1, 1), Order1:=xlAscending, _
Key2:=Cells(1, 2), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
Cells(1, 1).Select

End Sub

Reinhard_fb8cb7 · 10. November 2019 um 00:55

Hi Axel,
wegen dem Auto_Open, welche XL-Version? Ich kenne nur Worksheet_Open bzw. Auto_Open habe ich noch nie benutzt (XL97-2003).

Nach der Version frage ich auch, weil es bei Show ab XL2000 den Parameter modeless o.ä. gibt, vielleicht liegt es dadran, quien sabe.

Die IIf Zeile ist schon korrekt, aber falls die letzte Zelle in A belegt ist, knallt dein Code.

Spalte = 1
.Cells(loLetzte + 1, Spalte) = txtWert1.value
.Cells(loLetzte + 1, Spalte+1) = txtWert2.value
.Cells(loLetzte + 1, Spalte+2) = txtWert2.value
…

finde ich persönlich übersichtlicher.

Ansonsten kann ich deinen Code leider erst frühestens heute Abend testen.
Wie sieht denn dein Code aus wenn du die Sortierung auslagerst und in welcher Zeile kommt dort welcher Fehler?

Gruß
Reinhard

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

axel_o · 10. November 2019 um 00:55

Hallo Reinhard,

ich habe Excel 2003 SP2

Auto_Open bezeichnet das Makro, das ausgeführt wird, sobald die Datei in Excel geöffnet wird. Habe ich aber auch nur im Netz gefunden.
Auch die Ermittlung der letzten Zeile, die meisten Codes die ich dazu gefunden hatte, waren immer auf die in Excel allerletzte Zeile bezogen („A65536“). Um so was selbst zu schreiben, da reicht mein Knowhow noch lange nicht …

Du hast recht, die Spalte über +1 immer um einen zu verschieben ist übersichtlicher, da ich mit dem Layout der Tabelle noch nicht ganz fertig bin und immer noch mal eine Spalte eingefügt wird, war mit das immer zu viel, den Spaltenindex zu korrigieren.

Beim Auslagern der Prozedur weiß ich nicht, wie man den Wert der Variablen „loLetzte“ in das neue Sub bekommen kann. Das wäre wohl das einfachste. Deshalb habe ich die Ermittlung der letzten Zeile einfach noch mal ins Sub geschrieben.

Die Fehlermeldung kommt in Zeile 32 „Unzulässiger oder nicht ausreichend definierter Verweis“. Gelb markiert wird das erst „.rows“.

Vielen Dank schon mal,

Gruß Axel

Sub Auto_Open()
Load usrDatenEingabe
usrDatenEingabe.Show
End Sub

Private Sub cmdDatenSchreiben_Click()

Dim Spalte As Integer, loLetzte As Long

With ThisWorkbook.Worksheets(„Eingaben“)
loLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)

Spalte = 1
.Cells(loLetzte + 1, Spalte) = txtWert1.Value
Spalte = Spalte + 1
.Cells(loLetzte + 1, Spalte) = txtWert2.Value
Spalte = Spalte + 1
.Cells(loLetzte + 1, Spalte) = txtWert3.Value
Spalte = Spalte + 1
.Cells(loLetzte + 1, Spalte) = txtWert4.Value
Spalte = Spalte + 1
.Cells(loLetzte + 1, Spalte) = txtWert5.Value
End With

Call Sortieren

End Sub

Private Sub Sortieren()
Dim loLetzte As Long

loLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)

Range(Cells(1, 1), Cells(loLetzte + 1, 5)).Select
Selection.Sort _
Key1:=Cells(1, 1), Order1:=xlAscending, _
Key2:=Cells(1, 2), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
Cells(1, 1).Select

End Sub

Reinhard_fb8cb7 · 10. November 2019 um 00:56

Beim Auslagern der Prozedur weiß ich nicht, wie man den Wert
der Variablen „loLetzte“ in das neue Sub bekommen kann. Das
wäre wohl das einfachste. Deshalb habe ich die Ermittlung der
letzten Zeile einfach noch mal ins Sub geschrieben.

Die Fehlermeldung kommt in Zeile 32 „Unzulässiger oder nicht
ausreichend definierter Verweis“. Gelb markiert wird das erst
„.rows“.

Hi Axel,
Der Punkt vorne bei .Rows.count bezieht sich auf die With-Schleife, die fehlte im zweiten Code.
Rows.count bezieht sich auf das aktive Blatt
.Rows.count bezieht sich auf das Blatt der With-Schleife
Du benutzt beide Varianten, das ist nicht gut, falls das aktive Blatt nicht „Eingaben“ ist.
Also entweder in beide Subs eine Withschleifen, dann entsprechend die Punkte vor Cells, Rows usw setzen, oder halt die With-Schleife und die Punkte weg und sicherstellen daß „Eingaben“ das aktive Blatt ist,
worksheets(„Eingaben“).Aktivate

ungetestet, und noch mit nur einer Withschleife:

in das Dokumentmodul von " DieseArbeitsmappe":

Option Explicit

Private Sub Workbook_Open()
Load usrDatenEingabe
usrDatenEingabe.Show
End Sub

In das Standardmodul " Modul1"

Option Explicit
Public loLetzte As Long

Sub cmdDatenSchreiben\_Click()
Dim Spalte As Integer
With ThisWorkbook.Worksheets("Eingaben")
 loLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
 If loLetzte = .Rows.Count Then GoTo Fehler
 Spalte = 1
 .Cells(loLetzte + 1, Spalte) = txtWert1.Value
 .Cells(loLetzte + 1, Spalte + 1) = txtWert2.Value
 .Cells(loLetzte + 1, Spalte + 2) = txtWert3.Value
 .Cells(loLetzte + 1, Spalte + 3) = txtWert4.Value
 .Cells(loLetzte + 1, Spalte + 4) = txtWert5.Value
End With
Call Sortieren
Exit Sub
Fehler:
MsgBox "Spalte wegen Überfüllung geschlossen"
End Sub




Sub Sortieren()
 Range(Cells(1, 1), Cells(loLetzte + 1, 5)).Sort \_
 Key1:=Cells(1, 1), Order1:=xlAscending, \_
 Key2:=Cells(1, 2), Order2:=xlAscending, \_
 Header:=xlGuess, OrderCustom:=1, MatchCase:=False, \_
 Orientation:=xlTopToBottom, \_
 DataOption1:=xlSortTextAsNumbers, \_
 DataOption2:=xlSortNormal
 Cells(1, 1).Select
End Sub

Gruß
Reinhard

axel_o · 10. November 2019 um 01:05

Hallo Reinhard,

vielen Dank und sorry fuer die spaete Antwort auf Dein Posting.
Ich bin nur die Woche gerade ueber Irland…

Sobald ich wieder zu Hause bin werde ich Deine Tipps nutzen.

Axel

axel_o · 10. November 2019 um 01:10

Hallo Reinhard,

vielen Dank, Deine Tipps zum Code waren Spitze. Und erst recht die Idee mit der Fehlermeldung, wenn das Tabellenblatt voll ist.

Auch die Sortierung wird ordentlich gestartet. Excel scheint nur ein Problem mit dem „SortTextAsNumbers“ zu haben. Denn die erste Spalte enhält das Datum im Format „DD.MM.JJJJ“ (obwohl die Spalte entsprechend formatiert ist). Und mit den Monaten und Jahre kommt die Sortierung nicht ganz klar. Da werde ich noch mal etwas knoblen müssen.

Vielen Dank noch mal,

Axel