Office 2010: Probleme mit Resize-Methode bei ‚List Object‘

[sacoma]

Hallo Leute!

Ich bin am Verzweifeln. Wie schon in früheren Postings erwähnt, bin ich ein blutiger Anfänger in Excel-VBA. Um mein Problem zu erklären, muss ich etwas weiter ausholen…

Mein Ausgangspunkt:

Ich habe eine Excel-Datei erstellt, in dem die Aufträge meiner Firma übersichtlich verwaltet werden sollen (Im Anhang habe ich eine anonymisierte Muster-Tabelle mit den gleichen Sheets und VBA-Codes hinterlegt).
Momentan besteht sie aus 6 Arbeitsblättern (Sheets).

Aber für dieses Problem sind nur die Sheets „Auftrags_Liste“ und „Temp_Auftrags_Pos_Liste“ relevant. (Kleine Info: beide Sheet sind mit automatischer Tabellenformatvorlagen formatiert.)
Im Sheet „Auftragsliste“ ist oben links ein Button „Neue Eingabe“; über diesen Button öffnet sich ein Formal bzw. ein Dialog namens „Auftrags_Kopf_Eingabe“, in dem  man die Kunden-Bestell-Nr., Auftragsnr. und Lieferdatum eingibt. (Kleiner Hinweis: Die gelb unterlegten Textfelder müssen eingegeben werden, sonst erscheint eine MsgBox mit der Aufforderung das nachzuholen!)

Mit Klick auf Button „Pos. eingeben“ werden folgende Abläufe im Hintergrund ausgeführt:

1.   Die Daten aus dem Dialog „Auftrags_Kopf_Eingabe“ werden ins Sheet „Auftrags_Liste“ übertragen und

2.   der Dialog wird geschlossen, dann

3.   wird ein neuer Dialog namens „Auftrags_Pos_Eingabe“ geöffnet

Im Dialog „Auftrags_Pos_Eingabe“ sollen die einzelnen Positionen des Auftrages eingegeben werden.

Um die Auftrags-Positions-Eingabe so einfach und schnell wie möglich zu machen, habe ich ein Dropdown-Listenfeld eingefügt, die sich auf eine Artikel-Liste aus einem Range (eine formatierte Tabelle) im Sheet „QUELLE_Artikel_Info_Liste“ bezieht.
Im Dropdown-Listenfeld sucht man sich nun den Artikel aus und gibt die Menge ein, dann klickt man auf den Button „Hinzufügen“. Der eingegebene Datensatz erscheint im unteren Listenfeld und gleichzeitig im Sheet „Temp_Auftrags_Pos_Eingabe“ innerhalb der formatierten Tabelle (dafür sorgt eine Resize-Methode).

Nun zum Problem:

Man kann problemlos 2 bis 3 Auftragspositionen eingeben, danach erscheint eine Fehlermeldung „Laufzeitfehler ‚-2147417848 (80010108)‘ Die Methode ‚Resize‘ für das Objekt ‚List Object‘ ist fehlgeschlagen“ (siehe Bild „Fehlermeldung“ im Anhang; im 2. Bild „Bug“ sieht man welche Code-Zeiler den Fehler verursacht).

Ich habe verzweifelt versucht herauszufinden, warum diese Fehlermeldung erscheint, sie zu lösen oder zu umgehen… Ich glaube sogar die Ursache dafür zu kennen (siehe Link: https://support.microsoft.com/de-de/kb/178510), aber was ich auch tue, es gelingt mir nicht das Problem in den Griff zu bekommen!

Deshalb wende ich mich an die Excel-Programmier-Profis unter euch. Kann mir jemand von euch sagen, was da schief läuft?

Danke für die Hilfe im Voraus!

LG,

sacoma

[maninweb]

Hallo,

ungetestet, referenziere mal korrekt. Statt nur Range(...) das Tabellenobjekt davor ws_..._tab.Range(...).

Gruß

[sacoma]

Hallo maninweb!

Ich weiß leider nicht genau, was du meinst. 

Aber ich glaube, du willst, dass ich das "ws_Temp_Auftrags_Pos_Liste." vor dem "Range(...)" und nach "(...).Resize" einfügen soll, wie in diesem Beispiel hier:

##################################################

ws_Temp_Auftrags_Pos_Liste.ListObjects("Temp_Auftr_Pos_Liste").Resize _
ws_Temp_Auftrags_Pos_Liste.Range(ws_Temp_Auftrags_Pos_Liste.Cells(1, 1), ws_Temp_Auftrags_Pos_Liste.Cells(zeilen + 2, spalten))

##################################################

Ich muss leider sagen, dass das gar nichts gebracht hat.
Beim 2. oder 3. Mal kommt wieder die Fehlermeldung.

LG,

sacoma

[maninweb]

Hallo,

ohne Beispieldatei bzw. den restlichen Code ist das für mich leider nicht nachvollziehbar.

Gruß

[sacoma]

@maninweb:

Aber ich habe doch eine Beispiel-Datei und zwei Screenshot-Bilder angehängt!
Siehst du sie nicht?

LG,

sacoma

[gmg-cc]

Moin,
mal anders gefragt: Warum Resize? Eine Intelligente Tabelle erweitert sich von ganz alleine, wenn in ein direkt angrenzendes Feld etwas geschrieben wird.

Ehe du zu Recht anmerkst, dass das doch (wahrscheinlich) aus dem Code hervor geht: Ich habe im Moment nicht die Ressourcen, um meine VM zu nutzen, und Makro-behaftete Files öffne ich aus Gründen der Daten-Hygiene (Stichwort: Locky) nicht auf meinem Arbeitsrechner im "normelen" Umfeld.

[sacoma]

Hallo gmg-cc!

Tatsächlich war das so, dass sich die formatierte Tabelle sich allein erweiterte, sobald ein neuer Datensatz unten eingefügt worden ist. Aber dann hat das aufgehört und ich fand nicht heraus warum, wieso oder weshalb. Ich habe auch alles versucht den alten Zustand der automatischen Erweiterung wieder herzustellen, doch auch eine Aufhebung und Neu-Setzung der Tabellen-Formatierung brachte nichts.
Also habe ich den Makro-Recorder angeworfen und die Tabelle manuell runtergezogen und den generierten Code hier eingebaut:

##########################################
Private Sub BTN_Hinzufuegen_Click()

Dim artnr As String, ArtBez As String
Dim pos As Integer, text As String, f_str As String
Dim i As Integer, j As Integer, m As Integer, n As Integer, spalten As Integer, zeilen As Integer
Dim pos_schritte As Range, temp_pos_auftrdaten As Range


'Bereich, wo die Position fuer Listenfeld sind, uebergeben:
Set temp_pos_auftrdaten = Range("Temp_Auftr_Pos_Liste")
'Pos-Schritte übernehmen:
Set pos_schritte = Range("Pos_Schritte")
pos = pos_schritte

If CB_Art_Nr_eingeben = "" Then
text = "Artikel-Nr."
GoTo Fehlende_Eingabe
ElseIf TF_Menge = "" Then
text = "Menge"
GoTo Fehlende_Eingabe
Else
KdBestellNr = TF_KdBestellNr
Auftragsnr = TF_AuftragsNr
Liefertermin = TF_Liefer_Termin
artnr = CB_Art_Nr_eingeben '.Column(0)
ArtBez = TF_Artikel_Bez
Menge = TF_Menge
mgn_eht = TF_Mengeneinheit

'Spalten und Zeilen zaehlen:

spalten = temp_pos_auftrdaten.Columns.Count
Debug.Print "spalten: " & spalten
''ueberschreiben vorhandener Datensaetze vermeiden:
If zaehler = 0 Then
zeilen = temp_pos_auftrdaten.Rows.Count - 1 '+ 1
Else

zeilen = temp_pos_auftrdaten.Rows.Count
End If

'Tabelle bzw. pos_auftrdaten mit Daten füllen

With temp_pos_auftrdaten

Debug.Print "1.INFO: " & vbNewLine & _
"KdBestellNr: " & .Cells(zeilen, 1) & " TF_Pos: " & .Cells(zeilen, 2) & _
" artnr: " & .Cells(zeilen, 3) & " ArtBez: " & .Cells(zeilen, 4) & " Menge: " & .Cells(zeilen, 5) & _
" ME: " & .Cells(zeilen, 6) & " Liefertermin: " & .Cells(zeilen, 7)


'Kunden-Bestell-Nr.
.Cells(zeilen + 1, 1) = KdBestellNr
.Cells(zeilen + 1, 2) = TF_Pos
.Cells(zeilen + 1, 3) = artnr
.Cells(zeilen + 1, 4) = ArtBez
.Cells(zeilen + 1, 5) = Menge
.Cells(zeilen + 1, 6) = mgn_eht
.Cells(zeilen + 1, 7) = Liefertermin
Debug.Print "2.INFO: " & vbNewLine & _
"KdBestellNr: " & .Cells(zeilen + 1, 1) & " TF_Pos: " & .Cells(zeilen + 1, 2) & _
" artnr: " & .Cells(zeilen + 1, 3) & " ArtBez: " & .Cells(zeilen + 1, 4) & " Menge: " & .Cells(zeilen + 1, 5) & _
" ME: " & .Cells(zeilen + 1, 6) & " Liefertermin: " & .Cells(zeilen + 1, 7)
End With

''Range aktualisieren
''!!!!!!!!!!!!!!!!!!!!!!!!

ws_Temp_Auftrags_Pos_Liste.ListObjects("Temp_Auftr_Pos_Liste").Resize _
ws_Temp_Auftrags_Pos_Liste.Range(ws_Temp_Auftrags_Pos_Liste.Cells(1, 1), ws_Temp_Auftrags_Pos_Liste.Cells(zeilen + 2, spalten)) 'Schlaegt beim 3. Mal fehl!

Set temp_pos_auftrdaten = Range("Temp_Auftr_Pos_Liste")


n = n + 1
'Kopfzeile:

'Listenfeld füllen:
'******************
With LF_Bestell_Liste
'Anzahl der Spalten festlegen
   .ColumnCount = 5
   'Spaltenabstände festlegen
   .ColumnWidths = "30;50;80;140;50" 'Die erste Spalte wird mit Null ausgeblendet
   'Überschrift anzeigen
   .ColumnHeads = True
   .RowSource = temp_pos_auftrdaten.Address 'filter_auftrdaten.Address 'pos_auftrdaten.Address '.End(xlUp) '.Column(1)
   .Font.Size = 12
End With
zaehler = zaehler + 1
TF_Pos = TF_Pos + pos

End If

Set temp_pos_auftrdaten = Nothing
Exit Sub

Fehlende_Eingabe:
MsgBox "Es wurde keine " & text & " eingegeben!", vbCritical

End Sub
##########################################

LG,

sacoma

[maninweb]

Guten Morgen,

sorry, die Bilder habe ich gesehen, aber die Datei habe ich wirklich übersehen.

Habe mir mal den Code angeschaut und kann Fehler reproduzieren, jedoch nicht immer und auch an verschiedenen Stellen.
Das deutet darauf hin, dass generell einiges nicht sauber ist. Der Fehler kann z.B. auftauchen, wenn ein Resize erfolgt ist,
aber der von Dir verwendete Name Temp_Auftr_Pos_Liste nicht aktualisiert wurde. Dann stimmen Range(s) nicht mehr.
Ebenso taucht der Fehler ab und zu auf, weil Zeilen = 0 ist und Du im Debug.Print darauf zugreifst. Meines Erachtens
müsste der Code generell überarbeitet werden. Beispiele:

• Wofür App = CreateObject("Excel.Application")? Du erzeugst eine Excel-Instanz, die im Speicher rumhängt und
  nicht verwendet wird. Bitte rausnehmen.
• Objekte werden mit Set referenziert, nicht sp = kpf_auftr_daten.Columns sondern Set sp = kpf_auftr_daten.Columns
• Bitte spreche alle Eigenschaften explizit an, nicht die Defaults nehmen, also Cells(1,1).Value und nicht Cells(1,1) wenn
  Du den Wert der Zelle verwenden möchtest.
• Globale Variablen werden nur in Workbook_Open() initialisiert, z.B. ws_Temp_Auftrags_Pos_Liste. Die Variable wird z.B.
  beim Debuggen zurückgesetzt und steht Dir dann nicht mehr zur Verfügung. Initialisierung bitte immer vornehmen. In dem
  Fall (UserForms) würden eigentlich auch lokale Variablen reichen.
  

Gruß

[sacoma]

Hallo maininweb!

Dass ich ein blutiger Anfänger in Excel-VBA bin, habe ich ja geschrieben.
Vieles im Code habe ich mir aus Versatz-Stücken zusammengebastelt.
Aber ist das Excel-VBA wirklich so empfindlich?

Ich werde erst mal deine Liste mit Vorschlägen abarbeiten...

Danke für die Ratschläge!

LG,

sacoma

[maninweb]

Moin,

ja, dass Du Anfänger bist, habe ich schon wahrgenommen ;-)

Sagen wir mal so, VBA ist noch recht unempfindlich, gibt andere Programmiersprachen, die verzeihen nichts.
Dennoch, auch der VBA-Kompiler hat Grenzen.

Gruß

[sacoma]

Hallo Leute!

Ich habe nach  maninweb's Ratschlägen meine Excel-Datei überarbeitet.
Aber der oben beschriebene Fehler bei der Resize-Methode taucht immer noch nach wie vor auf.
Es gibt keine Verbesserung zur Ausgangsposition.

Wo liegt hier also der Fehler?

Im Anhang ist die überarbeitete Datei "Muster-Tabelle_ueberarbeitet_21-07-2016".

LG,

sacoma

[gmg-cc]

Moin,
ich konnte das Problem (mit anderen Fehlermeldungen als von dir dargestellt) nachvollziehen. Was mich ratlos macht: Gehe ich den Code im Editor ab der Fehlermeldung Schritt für Schritt (F8) durch, dann klappt das problemlos. 

Ich würde das Ganze erst einmal in der Maske erfassen und parallel nicht in eine (physische) Tabelle schreiben sondern in ein Array (da gibt es auch ein Resize). Den Inhalt dann komplett in eine Temp-Tabelle oder wohin auch immer ...

Was mir noch aufgefallen ist ... (teilweise unabhängig von der Fehlermeldung)

• Im Modul BTN_Hinzufuegen_Click fehlen die Initialisierungen für die Variablen
  WB, ws_Auftrags_Liste, ws_Auftrags_Pos_Liste, ws_Temp_Auftrags_Pos_Liste, ws_Auftrags_Management (Das könnte es schon sein!)
• Beim Muss-Feld "Liefer-Termin" kann ich Unsinn eingeben, hier wäre eine IsDate()-Prüfung sinnvoll
• Die extrem langen VariablenNamen machen das Ganze nicht lesbarer ...
• Bei "Menge" führt ein "A" (oder anderen non-numerischen Zeichen) zum Abbruch

Nimm es bitte nicht als destruktive sondern als konstruktive Kritik, ich habe auch einmal angefangen ...

[sacoma]

Hallo gmg-cc!

Ich nehme mir gar nichts zu herzen und bin für jeden Tipp dankbar.

Momentan bin ich etwas verwirrt: 

Im Modul BTN_Hinzufuegen_Click fehlen die Initialisierungen für die Variablen
WB, ws_Auftrags_Liste, ws_Auftrags_Pos_Liste, ws_Temp_Auftrags_Pos_Liste, ws_Auftrags_Management (Das könnte es schon sein!)

Diese Variablen werden doch initialisiert:

Aus dem Code:

Private Sub BTN_Hinzufuegen_Click()

Dim artnr As String, ArtBez As String 'KdBestellNr As String, Auftragsnr As String, Liefertermin As String
Dim pos As Integer, text As String, f_str As String
Dim i As Integer, j As Integer, m As Integer, n As Integer, spalten As Integer, zeilen As Integer
Dim pos_schritte As Range, temp_pos_auftrdaten As Range

Set WB = Application.ActiveWorkbook
Set ws_Auftrags_Liste = WB.Worksheets("Auftrags_Liste")
Set ws_Auftrags_Pos_Liste = WB.Worksheets("Auftrags_Pos_Liste")
Set ws_Temp_Auftrags_Pos_Liste = WB.Worksheets("Temp_Auftrags_Pos_Liste")
Set ws_Auftrags_Management = WB.Worksheets("Auftrags_Management")

[...]

Oder verstehe ich das Wort "Initialisierung" falsch?

LG,

sacoma

[gmg-cc]

Mea culpa, du hast Recht !
Diese drückende Hitze und Schwüle macht mich ganz kirre 
Ich meinte DIMENSIONIEREN und nicht initialisieren 

Im Moment bin ich auch ziemlich abgeschlafft, 6 Stunden (auch in der Mittagshitze) mit einem 3-jährigen sehr agilen Enkel. Vielleicht habe ich auch hier etwas übersehen wie globale Dimensionierung?