Die Vorschlagslisten in Excel sind perfekt geeignet, um die Eingabe zu beschleunigen. Haben Sie bereits ein paar Datensätze eingegeben, so wird z. B. in der Spalte Ort in der folgenden Abbildung ...
... gleich ein Vorlag für den Ortnamen unterbreitet, sofern die Liste oberhalb einen eindeutigen Vorschlag liefern kann.
Genau hier, soll das folgende Makro ansetzen. Tatsächlich schlägt Excel erst etwas vor, wenn oberhalb der aktiven Zelle bereits ein solcher Eintrag vorhanden ist.
Der Wunsch soll es nun sein, dass die Funktion der Vorschlagslist sofort, für eine vordefinierte Liste bei einer neuen leeren Kopie des Blattes aktiviert ist.
D. h. Sie starten mit einem leeren Blatt, nur die Spaltenüberschriften (Anrede, Vorname, Nachname, usw.) sind vorhanden und Excel schlägt schon ein paar Orte vor. Soweit zum Ziel der Übung.
Ich nutze für den Start folgendes Beispiel:
Ein Verwaltungsblatt für Spielzeug, dass eine Spalte "Spielzeug" und eine Spalte "Vorfall" hat. In der Spalte Spielzeug, zeigt die Vorschlagsliste sofort einige Vorschläge an (Ball, Holzeisenbahn, Lego, usw.) ohne, dass diese zuvor eingegen werden mussten.
Der Trick:
Sie sehen, dass die Liste erst ab der Zeile 50 startet. Die Zeilen 1 bis 49 sind ausgeblendet. Klar, dass sich in den ausgeblendeten Zeilen die Listeneinträge verbergen. Warum 49 Zeilen? Damit Sie genug Platz für Vorschläge der Liste haben.
Wer mehr Vorschläge benötigt nutzt die ersten 200 Zeilen oder auch mehr, um die Vorschlage hineinzubringen. Die Leeren Zellen werden dann mit dem deutschen Apostroph bzw. Hochkommata (') ausgefüllt.
In der nächsten Abbildung sehen Sie die ersten Zeilen eingeblendet:
Die Spalte G enthält von G1 bis G4 ein paar Einträge (die auch unten in der Liste später sichtbar werden). Ab der Zelle G5 (für die Liste sind derzeit keine weiteren Einträge nötig) bis zur Zelle G49 wird ein Hochkommata eingetragen.
Das Hochkomma hat den Vorteil, dass es selbst nicht angezeigt oder gedruckt wird. Trotzdem ist es ein Eintrag in der Zelle. Damit wird die Liste bis unten zur Zeile 50 nicht unterbrochen und die Vorschlagsliste arbeitet einwandfrei.
Die Zeilen 1 bis 49 werden dann ausgeblendet. In den Zellen G50 und G51 sind ebenfalls Hochkommata enthalten. Wird nun in der Zelle G53 ein Eintrag getätigt, ...
... ist die Vorschlagsliste bereit mit den ausgeblendeten Werten bestückt.
OK, die Leerzeile kommt durch das Hochkommata und der Eintrag "Spielzeug" ist die Überschrift der Spalte in Zelle G52.
Trotzdem funktioniert das Beispiel. Das soll zunächst die Idee sein.
Was nun hinzukommt, ist dass es für jeden Ort ein eigenes Tabellenblatt gibt. Wird ein Ort hinzugefügt, wird ein leeres Blatt erstellt, auf dem die Vorfälle abgetragen werden können.
Dieses "leere" Blatt wird mit "Vorlage" beschriftet.
Wird ein neuer Ort hinzukommen, wird das Vorlagenblatt (mit allen Einträgen in den Zeilen 1 bis 49) einfach kopiert und als "K01", "K02", "K03", usw. angeboten.
Damit sind alle Einträge auch in diesen Blättern.
Fertig.
Was aber nun, wenn Sie die Einträge der Vorschlagsliste "Spielzeug" ändert oder einträge hinzukommen? Dann muss man in jede einzelne Tabelle und diese Einträge ergänzen. Das ist sehr aufwändig.
Die Lösung für diesen Fall schafft ein Excel Makro.
Ich lege vor die Tabellenblätter ein Blatt "Listen" an, das eine Hilfsliste für das Spielzeug ... Spalte D ... enthält (zusätzlich noch eine Liste für Vorfälle ... Spalte F ..., dass aber nur als Idee für weitere Vorschlagsfelder).
Dann erstelle ich mir über die Entwicklertools ein neue "Schaltfläche" ...
... die beim Klick ein "Neues" Makro ausführen soll.
Der Klick auf "Neu" erstellt ein neues Modul in der Excel Arbeitsmappe.
Füllen Sie das Modul mit folgendem Code:
{xtypo_code}Sub Schaltfläche6_Klicken()
Dim int_anz_tabellenblaetter As Integer
Dim int_akt_tabellenblatt As Integer
Dim int_akt_zelle As Integer
'Ermittelt die Anzahl der Tabellenblaetter
int_anz_tabellenblaetter = Sheets.Count
For int_akt_tabellenblatt = 1 To int_anz_tabellenblaetter
If Sheets(int_akt_tabellenblatt).Name <> "Listen" Then
'Laeuft die Zeile 1 bis 49 durch
For int_akt_zelle = 1 To 49
'Kopiert die Zellen D1 bis D49 aus dem Tabellenblatt "Liste" in
'die Zellen G1 bis G49 der uebrigen Tabellenblaetter
If Sheets("Listen").Range("D" & int_akt_zelle) <> "" Then
Sheets(int_akt_tabellenblatt).Range("G" & int_akt_zelle) = _
Sheets("Listen").Range("D" & int_akt_zelle)
Else
Sheets(int_akt_tabellenblatt).Range("H" & int_akt_zelle) = "'"
End If
'Kopiert die Zellen F1 bis F49 aus dem Tabellenblatt "Liste" in
'die Zellen H1 bis H49 aus dem Tabellenblatt Liste in
If Sheets("Listen").Range("F" & int_akt_zelle) <> "" Then
Sheets(int_akt_tabellenblatt).Range("H" & int_akt_zelle) = _
Sheets("Listen").Range("F" & int_akt_zelle)
Else
Sheets(int_akt_tabellenblatt).Range("H" & int_akt_zelle) = "'"
End If
Next int_akt_zelle
End If
Next int_akt_tabellenblatt
End Sub{/xtypo_code}
Beachten Sie, dass der Name der Prozedur bei mir "Schaltfläche6_Klick" heißt. Bei Ihnen könnte die Schaltfläche die Schaltfläche "3" sein, so kann der Name der Prozedur durchaus abweichen.
Schließen Sie den VBA Editor und klicken sie auf die Schaltlfäche. Damit wird der Code ausgeführt und auf allen Arbeitsblättern, bis auf dem Blatt "Listen" werden die Einträge aus den Zellen "D1 bis D49" des Blattes "Listen" auf die übrigen Blätter der Arbeitsmappe übertragen. Das selbe passiert auch mit den Zellen F1 bis F49, diese werden auf alle Blätter in die Zellen H1 bis H49 übertragen.
Damit wäre auch dieses Problem gelöst. Kommt zukünftig ein Eintrag hinzu, so geben Sie diesen im Blatt "Listen" ein und betätigen die Schaltfläche. Damit werden die Einträge auf allen Listen aktualisiert und Sie können sofort in jeder Liste mit den neuen Einträgen arbeiten.
ENDE DES WORKSHOPS
EDV Workshops
Office im Büroalltag