HOME WORKSHOPS BEFEHLSREFERENZ API-REFERENZ FAQ FORUM DOWNLOAD TOOLBESPRECHUNG BÜCHERECKE MARKETPLACE GRAFIK & DESIGN VB-SEITEN

Partnerseiten
Top 5 Partnerlinks







Weitere...

Entwickler / Welt

··· Tipps & Tricks · Microsoft Visual Basic for Application (VBA) · Excel

Labels (Etiketten) erstellen mit Excel (VBA) Hier erfahren Sie, wie Sie aus Excel schnell und einfach ein Etikettendruckprogramm machen ;-)
Autor:   Heinz Wölfel	Bewertung:     [ Jetzt bewerten ]	Views:  34.995
ohne Homepage	System:  Win9x, WinNT, Win2k, WinXP, Win7, Win8, Win10, Win11	Beispielprojekt auf CD

Etiketten erstellen, aber wie?:
Um "richtig" Etiketten (engl. Labels) zu erstellen bzw. zu drucken nimmt man normalerweise ein Datenbankprogramm (teuer), ein spezielles Zusatzprogramm oder ein Add-In (umständlich und meist etwas unflexibel) oder die "Umschläge-Etiketten-Funktion" von WinWord (Buch mit sieben Siegeln). Wenn's aber nur wenige Adressen sind bzw. wir vor allem flexibel sein wollen, machen wir das selbst, und logischerweise in VBA .

Das Arbeitsblatt für die Adressen:
Die Daten für die Etiketten (i.d.R. Adressen) müssen in einem Excel-Blatt (engl. Sheet) zeilenweise vorliegen. Im Beispiel zum Herunterladen sind schon einige Beispieladressen enthalten, die man durch die eigenen Adressen ersetzt. Weiterhin kommt auf dieses Excel-Blatt logischerweise ein Button zum Starten des VBA- Makros.

Übrigens: Wichtig ist, dass in der A-Spalte Zahlen oder irgendwelche Zeichen sind (z.B. die Mitgliedsnummer im Kegelverein). Im Makro werden aus den Adressen-Zeilen solange Etiketten erstellt bis dieses im Blatt "TEST_Adressen" in der A-Spalte auf eine leere Zelle trifft. Diese Nummern/Zeichen kommen übrigens rechts oben mit auf die Etiketten.

Die eigentlichen Adressen werden aus den Spalten C bis J ab der 2-ten Zeile entnommen. Falls diese Zeilen- und Spaltenaufteilung geändert wird, muss natürlich das Makro angepasst werden.

Zwei Sonderfunktionen werden über die B-Spalte gesteuert:

• Was macht man, wenn man einzelne Adressen überspringen will, d.h. von bestimmten Zeilen sollen keine Etiketten erstellt werden? Einfach ein ü oder Ü (ü für überspringen) in die entsprechende B-Zelle einfügen.

• Wenn man dagegen einen gebrauchten Etiketten-Bogen verwenden will, bei dem z.B. schon die ersten vier Etiketten fehlen? Ab der Reihe 2 setzt man 4 Leerzeilen ein und gibt in diesen Zeilen in der B-Zelle x oder X (x für nix drucken) ein. Dies funktioniert übrigens bei allen Zeilen, nicht nur am Anfang.

Alle anderen Zeichen in den Zellen der B-Spalte werden ignoriert.

Das Arbeitsblatt für die Etiketten:
Die vom Makro erstellten Etiketten (z.B. 3 Spalten; 8 Reihen pro Seite) werden in einem separaten Blatt (Sheet) mit dem Namen "Labels" erstellt. Dieses Blatt wird angelegt, falls es noch nicht vorhanden ist, andernfalls wird der Inhalt ohne Nachfrage gelöscht. Die Formatierung der Spaltenbreiten und der Seitenränder werden angepasst. 

Ausdrucken:
Ein Ausdruck durch das Makro erfolgt jedoch NICHT, damit man noch letzte Formatierungen vornehmen kann. Wenn alles passt, macht man das Ausdrucken "von Hand". Das Excel-Sheet mit den Labels drucke ich dann der Einfachheit halber auf ganzseitige Etiketten in A4-Format (z.B. Zweckform Nr. 4735) und zerschneide sie hinterher. 

Das Makro ist jedoch auf Bögen mit 3*8 Etiketten hin optimiert (z.B. Zweckform Nr. 3490). Da jedoch jeder Drucker etwas andere (nicht bedruckbare) Ränder hat, sind ggf. noch Feineinstellungen nötig. Wichtige Code-Zeilen hierfür sind mit "'^^^^^^^^^^^^^" gekennzeichnet. Am einfachsten ist es sicherlich, die Zeilenhöhe der Leerzeile, die nach jedem 3-ten Label eingefügt wird Selection.RowHeight = 34 etwas anzupassen.

Wenn jemand andere Bögen mit einer anderen Etiketten-Anzahl verwenden will müssen zusätzlich noch die Abfragezeilen If i_Label_nummer Mod 3 = 0 Then und If i_Label_nummer Mod 24 = 0 Then im Makro angepasst werden.

Die VBA- Lösung:
Das Erstellen der Etiketten übernimmt ein relativ übersichtliches VBA-Makro (mit vielen Kommentarzeilen und sollte durchaus auch für Anfänger überschaubar sein. 

Vor dem Erstellen der Etiketten wird nach einer Sicherheitsabfrage das Programm gestartet, das "Labels"-Blatt gelöscht, sowie Zeilenhöhe, Spaltenbreite und Seitenrand eingestellt. Die Adressen werden dann zeilenweise vom Blatt "TEST_Adressen" in das Blatt "Labels" übertragen, bis eine leere Zelle in Spalte A auftaucht (siehe oben). 

Auf jedes Etikett kommt untereinander der Absender (dies ist über eine Konstante am Programmanfang definiert), der A-Spalten-Text und die Adresse. Vorname/Nachname und PLZ/Ort werden verbunden und jeweils in die gleiche Zeile eingefügt. 

Zwischen die Etiketten einer Reihe wird eine Leerzeile eingefügt und nach 8 Reihen wird ein Seitenvorschub gesetzt. Zum Schluss speichert das Makro die Arbeitsmappe.

Das Makro "Labels_erstellen()" muss sich in einem Modul befinden.

...siehe ausführlich kommentierten VBA-Code in der Excel-Datei (Demoprojekt)...

Der Aufruf erfolgt im Adressen-Blatt mit:

Private Sub CommandButton1_Click()
  Labels_erstellen
End Sub

Das Makro anpassen:
Ich meine, es ist nicht allzu schwer das Programm abzuändern, falls nötig. Man orientiert sich einfach an den Kommentarzeilen. Wenn z.B. beim Aufnehmen der Adresse die Spalten im Adressen-Blatt anders angelegt sind, muss der Schleifenanfang geändert werden:

For h% = 1 To 10
  ...
Next

Wenn jedoch beim Ablegen der Adresse im Label-Blatt z.B. keine Mitgliedsnummer gewünscht wird, wird die Zeile:

s_adresse(1) = "-" & s_adresse(1) & "-"

zu

s_adresse(1) = ""

abgeändert. Wer das Entstehen der Labels sehen möchte kann die Zeile

Applicaton.ScreenUpdating = False

weglassen. Weitere mögliche Änderungen wurden bereits weiter oben aufgezeigt.

Und nun viel Spaß beim Etiketten erstellen.

 

Microsoft, Windows und Visual Basic sind entweder eingetragene Marken oder Marken der Microsoft Corporation in den USA und/oder anderen Ländern. Weitere auf dieser Homepage aufgeführten Produkt- und Firmennamen können geschützte Marken ihrer jeweiligen Inhaber sein.

Diese Seiten wurden optimiert für eine Bildschirmauflösung von mind. 1280x1024 Pixel