Wenn aus einer Datenbank heruntergeladene Daten den Bereich eines Kombinationsfelds überschreiten, werden die neuen Elemente einfach nicht angezeigt. Um dem entgegenzuwirken, muss der Bereich, der den Listen- oder Kombinationsfeldern zugrunde liegt, erweitert oder verkleinert werden, um mit den Daten übereinzustimmen. In diesem Artikel wird untersucht, wie dies automatisch durchgeführt wird.
Es wird davon ausgegangen, dass dem Leser das Entwicklerband angezeigt wird und er mit dem VBA-Editor vertraut ist. Wenn nicht, klicken Sie auf Google "Excel Developer Tab" oder "Excel Code Window".
Eine professionelle Möglichkeit, Kombinationsfelder anzuzeigen, besteht darin, die Sortimente nach Bedarf zu erweitern oder zu verkleinern. Zum Beispiel:
Der Schlüssel zu einem Dynamikbereich ist die Überwachung der Anzahl der ausgefüllten Zeilen in der entsprechenden Spalte mithilfe der Funktion = countA. Diese Funktion zählt aufgefüllte Elemente in einer Folge von Zellen, bis sie das letzte treffen. Im Fall des ersten Diagramms im obigen Bild wäre dies Zeile 11.
Um einen Bereich automatisch zu verwalten, sind definierte Namen erforderlich, um die Anzahl der ausgefüllten Zeilen zu verfolgen. Zum Beispiel verwenden wir eCol (Endspalte) und eRow (Endzeile), um die Grenzen unseres Bereichs zu definieren. Je mehr Zeilen wir füllen, desto größer wird der Wert von eRow.
Die oben definierten Namen haben die Liste durch die Anzahl der in eCol ausgefüllten Zeilen (eRow = 1) auf eine Spalte (eCol = 11) festgelegt.
Schließlich erscheinen die Titel im Bereich „A2: A“ & eRow. Sie werden feststellen, dass die Indexfunktion verwendet wird, um die letzte Zelle im Bereich "Titel" einzurichten. Tatsächlich wird der Bereich „A2: A“ & eRow zu diesem Zeitpunkt in „A2: A11“ übersetzt.
Wir können den Dynamikbereich beim Öffnen der Arbeitsmappe automatisch einrichten, indem wir die Unterprozedur Auto_open verwenden, die ausgeführt wird, bevor die Arbeitsmappe sichtbar wird.
Der Code des Chamäleons
Öffnen Sie eine Arbeitsmappe und füllen Sie sie mit einem Kombinationsfeld und einigen Daten. Die in dieser Übung verwendete Beispielarbeitsmappe finden Sie werden auf dieser Seite erläutert.
Öffnen Sie das VBA-Codefenster und fügen Sie ein Modul ein. Kopieren Sie den folgenden Code in das Modul.
Das Auto_Open-Ereignis legt die Werte für die letzte Zeile und die letzte Spalte für den Dynamikbereich „Titel“ fest und notiert nachfolgende Änderungen.
Sub auto_open()
Dim eRow As Integer, eCol As Integer, i As Long
On Error Resume Next
'Clear the present define names, to avoid any duplications
activeworkbook.Names("eCol").Delete
activeworkbook.Names("eRow").Delete
activeworkbook.Names("Titles").Delete
Range("A1").Select
'Titles will appear in the first column, A in this case
eCol = 1
'Find the last populated row
eRow = Sheets("Main").Cells(Rows.Count, eCol).End(xlUp).Row
'Define the names
activeworkbook.Names.Add Name:="eCol", RefersTo:="=COUNTA($1:$1)"
activeworkbook.Names.Add Name:="eRow", RefersToR1C1:="=COUNTA(C" & ColNo & ")"
activeworkbook.Names.Add Name:="Titles", RefersTo:="=A2:INDEX($2:$200," & "eRow," & "eCol)"
End Sub
Sub DropDown1_Change()
MsgBox "Directed by " & Cells(2, 5)
End Sub
Hinweis: Zur besseren Übersichtlichkeit befindet sich alles auf einer einzigen Seite. Normalerweise würden sich die Werte der Spalten A und B sowie D und E auf einem separaten, möglicherweise ausgeblendeten Tabellenblatt befinden. Bitte beachten Sie außerdem, dass die zweite Spalte (B) in dieser Übung nicht zur Definition des Bereichs verwendet wird. Die Werte von Spalte B werden wie üblich über die Zellenverknüpfung des Kombinationsfelds ermittelt (D2 entspricht der Auswahl des dritten Elements im Bereich, der bei A2 beginnt, in Verbindung mit der INDEX-Funktion in „E3“, um den Bereich zu finden: =INDEX(B:B;D2+1;1)).
Speichern Sie die Arbeitsmappe und öffnen Sie sie erneut. Das Kombinationsfeld wird zunächst mit Auto_open gefüllt. Fügen Sie Elemente zu Spalte A und B hinzu und beobachten Sie die Änderungen am Kombinationsfeld.
Beschädigte Excel-Dateien retten
Von Zeit zu Zeit können Excel-Dateien beschädigt werden, nachdem Excel unerwartet abgestürzt ist. Wenn Sie ein Backup haben, können Sie die Daten einfach mit Ihrem Backup wiederherstellen. Andernfalls müssen Sie möglicherweise einen professionellen Experten oder ein Tool suchen, um das Problem zu beheben beschädigtes Excel Dateien.
Einführung des Autors:
Felix Hooker ist ein Datenrettungsexperte in DataNumen, Inc., das weltweit führend bei Datenwiederherstellungstechnologien ist, einschließlich seltene Reparatur und SQL Recovery-Softwareprodukte. Für weitere Informationen besuchen Sie www.datanumen.com €XNUMX