Veröffentlicht am von Stefan

Flexible Beschriftung von Zellen

In Excel sollen mehrere Zellen mit einem unterschiedlichen Wert befüllt werden.

Anforderung

  • Die Beschriftung muss flexibel gestaltet werden, so dass Sie schnell geändert werden kann
  • Der Inhalt muss kopiert werden können

Die Verwendung einer Formel, wie z.B. ist unmöglich, da beim Kopieren der Wert nicht übernommen werden kann. Die kopierte erzeugt einen Fehler. Es bleibt also nur das VBA-Makro, um die Zellen zu beschriften.

Beispiel

Im Screenshot ist die Zeile, die mit dem Makro benannt werden soll farblich hervorgehoben.

In jeder Zelle, C7, D7, E7, etc., ist ein Überblick zu bekommen, wie viele Stellplätze in Summe belegt sind. Die Beschriftung wird mit Hilfe eines VBA-Makros vorgenommen.

Damit man Punkt 1 der Anforderung erfüllen kann, also eine flexible Gestaltung der Tabelle gewährleistet, wurden die Zellen umbenannt. Dazu wurde in das Namensfeld ein neuer Wert eingetragen. Für die Zelle C7 wurde der Name Kap_7_3, für Zelle D7 der Name Kap_7_2, usw. eingetragen.

Die Umbenennung ist unbedingt vorzunehmen. Wird im Makro die Zelle nur mit C7 angesprochen und eine neue Zeile eingefügt, dann verschiebt sich der neue Wert im Makro nicht, wie in den Excel-Formeln, d.h.
C7, D7, E7, F7, G7
wird beim einfügen einer neuen Zeile NICHT zu
C8, D8, E8, F8, G8.
Das Makro spricht weiterhin die Zellen C7, D7, E7, F7, G7. Durch die Vergabe neuer Namen wird das Problem umgangen. Wird eine neue Zeile eingefügt, spricht das Makro weiterhin die richtigen Zellen an.

Code

Option Explicit

Sub KapBennennung()

Dim strRegal7 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal7 = "Regal 7"

Dim strRegal6 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal6 = "Regal 6"

Dim strRegal5 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal5 = "Regal 5"

Dim strRegal4 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal4 = "Regal 4"

Dim strRegal3 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal3 = "Regal 3"

Dim strRegal2 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal2 = "Regal 2"

Dim strRegal1 As String             ' Zur Beschriftung der Zellen mit Regal X
strRegal1 = "Regal 1"

Dim strNum1 As String               ' Zur Beschriftung der Zellen mit -1
strNum1 = "-1"

Dim strNum2 As String               ' Zur Beschriftung der Zellen mit -2
strNum2 = "-2"

Dim strNum3 As String               ' Zur Beschriftung der Zellen mit -3
strNum3 = "-3"


Sheets("Kapazitaet").Select

    Range("Kap_7_3").Value = strRegal7 & strNum3    ' Beschriftet die Zelle Kap_7_3 --> Regal 7-3
    Range("Kap_7_2").Value = strRegal7 & strNum2    ' Beschriftet die Zelle Kap_7_2 --> Regal 7-2
    Range("Kap_7_1").Value = strRegal7 & strNum1    ' Beschriftet die Zelle Kap_7_1 --> Regal 7-1
    
    Range("Kap_6_3").Value = strRegal6 & strNum3
    Range("Kap_6_2").Value = strRegal6 & strNum2
    Range("Kap_6_1").Value = strRegal6 & strNum1

    Range("Kap_5_3").Value = strRegal5 & strNum3
    Range("Kap_5_2").Value = strRegal5 & strNum2
    Range("Kap_5_1").Value = strRegal5 & strNum1
    
    Range("Kap_4_3").Value = strRegal4 & strNum3
    Range("Kap_4_2").Value = strRegal4 & strNum2
    Range("Kap_4_1").Value = strRegal4 & strNum1
        
    Range("Kap_3_3").Value = strRegal3 & strNum3
    Range("Kap_3_2").Value = strRegal3 & strNum2
    Range("Kap_3_1").Value = strRegal3 & strNum1
    
    Range("Kap_2_3").Value = strRegal2 & strNum3
    Range("Kap_2_2").Value = strRegal2 & strNum2
    Range("Kap_2_1").Value = strRegal2 & strNum1
    
    Range("Kap_1_3").Value = strRegal1 & strNum3
    Range("Kap_1_2").Value = strRegal1 & strNum2
    Range("Kap_1_1").Value = strRegal1 & strNum1

    Range("A1").Select
    
End Sub

Erklärung zum Code

Im ersten Abschnitt wird eine Variable für jedes Regal angelegt, also

  • strRegal7 erhält den Namen Regal 7
  • strRegal6 erhält den Namen Regal 6
  • …
  • strRegal1 erhält den Namen Regal 1

Im zweiten Abschnitt werden die Nummern 1, 2, und 3 einer Variablen zugeordnet

  • strRegal1 erhält den Namen -1
  • strRegal2 erhält den Namen -2
  • strRegal3 erhält den Namen -3

Nachdem die Variablen angelegt und einen festen Namen erhalten haben, kann man nun beginnen, die Zellen zu Beschriften.

Zuerst wird das Tabellenblatt aufgerufen, dass die Werte enthalten soll. Das ist besonders bei mehreren Tabellen in einer Arbeitsmappe erforderlich, um zu vermeiden, dass nicht aus versehen eine falsche Beschriftung vorgenommen wird und keine anderen Werte überschrieben werden.
Der Aufruf der Tabelle erfolgt mit dem Befehl

Sheets("Kapazitaet").Select

Nun kann man beginnen die Beschriftung zu definieren:

    Range("Kap_7_3").Value = strRegal7 & strNum3    ' Beschriftet die Zelle Kap_7_3 --> Regal 7-3
    Range("Kap_7_2").Value = strRegal7 & strNum2    ' Beschriftet die Zelle Kap_7_2 --> Regal 7-2
    Range("Kap_7_1").Value = strRegal7 & strNum1    ' Beschriftet die Zelle Kap_7_1 --> Regal 7-1

Die Funktionsweise ist ziemlich einfach.
Zu Beginn definiert man die Range, also die Zelle, die man beschriften will. Dazu ruft man nicht die Standard-Excelbezeichnung C7 auf, sondern den neuen Namen Kap_7_3. Nach dem Gleichheitszeichen (=) fügt man dann beide definierten Variablen strRegal7 und strNum3 zusammen. Im Tabellenblatt ergibt das dann den Wert Regal 7-3.
Mit den restlichen Tabellen verfährt man genau gleich, wie oben beschrieben.

Wendet man dieses Vorgehen zur Beschriftung an, dann sind lediglich ein paar Werte im VBA-Code zu ändern um den Zellen eine andere Beschriftung zu verpassen.

Der Befehl, bevor das Makro mit dem Befehl End Sub beendet wird setzt der Cursor in die Zelle A1.
In der VBA-Sprache heißt der Befehlt

Range("A1").Select

Weitere Posts:

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert