Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Jeder Zelle einer Tabelle automatisch Namen geben

Betrifft: Jeder Zelle einer Tabelle automatisch Namen geben von: Barbara
Geschrieben am: 30.07.2014 20:02:38

Hallo

ich würde gerne jede Zelle einer Tabelle mit einem Namen versehen, der aus den Zellen der ersten Spalte und obersten Zeile zusammen gesetzt ist.

Unten ein Beispiel, das sich aber auf einen bestimmten Bereich bezieht, das ich in diesem Forum _
gefunden habe.



Sub ErstelleNamen()
Dim Rng As Range, C As Range
Set Rng = Range("B5:P11")
For Each C In Rng
 C.Name = Cells(C.Row, 1) & "_" & Cells(1, C.Column)
Next
Set Rng = Nothing
End Sub

Kann man das so ändern, dass es sich auf eine bestimmte Tabelle bezieht. Sie hat den Namen "Bereich".

Außerdem sollen sich die Namen automatisch aktualisieren, sobald ich in einer namensgebenden Zelle in der ersten Spalte oder Zeile einen anderen Text eintrage. Dabei sollen die verwaisten Namen gelöscht werden.

Es soll eine Meldung kommen, wenn
- der Eintrag zu einer unzulässigen Namensbildung führt (wenn zum Beispiel ein Leerzeichen darin vorkommt). Aber die Namensgebung soll fortgesetzt werden. Denn sonst könnte man nicht die Tabelle vergrößern, weil ja da automatisch ein leerer Zeilen- oder Spaltenkopf entsteht.
- der Name bereits vergeben ist, wobei dieser bestehen bleiben soll. Dann muss man eben was anderes in diese Zelle schreiben.

Vielen Dank.

LG,
Barbara

  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Michael
Geschrieben am: 31.07.2014 12:41:43

Hallo Barbara,

ich möchte Dich nicht verunsichern, habe aber das Gefühl, daß Du die Sache grundsätzlich etwas umständlich angehst.

Mit Namen für Bereiche zu arbeiten, ist für den Programmierer oft eine Arbeitseinsparung, weil er anstatt umständliche Ausdrücke zu verwenden, direkt mit dem Namen arbeiten kann.

Was Du vorhast, klingt für mich aber gerade nicht nach Arbeitseinsparung.

JEDER Zelle (ausgenommen 1. Zeile/Spalte) einen Namen zu geben, bremst meiner Ansicht nach das System aus (Du willst ja entsprechende Meldungen erzeugen, bei Änderungen laufend durchrechnen usw.), mit Sachen, die mit der "eigentlichen" Lösung nichts zu tun haben (oder zu haben scheinen - ich weiß ja nicht, was Du insgesamt vorhast).

Jede Zelle HAT ja im Prinzip bereits einen Namen, nämlich A1, A2 usw., was für entsprechende Manipulationen völlig ausreicht.

Du schreibst ja, daß Du VBA nur mit Rekorder beherrscht. Ich empfehle Dir, Dich ein paar Stunden mit den Möglichkeiten von Range zu beschäftigen, insbesondere den Eigenschaften Culomn(s), Row(s), Offset
und den Methoden copy, pastespezial, clear und vielleicht find.

Das braucht man wirklich laufend, und vielleicht löst es Dein Problem "automatisch".

Du kannst natürlich gerne kurz schildern, in welchem Zusammenhang Du Dich dazu entschlossen hast, Namen zu vergeben.

Schöne Grüße,

Michael


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Adis
Geschrieben am: 31.07.2014 15:48:20

Hallo

Mit Namen habe ich immer nur den Anfang bestimmter Bereiche versehen, als zentralen Bezugs-Punkt.
Von dort aus kann man mit Cells() weiter arbeiten: z.B. Range("Einkauf").Cells(1,1) = Wert1

Jede einzelne Zelle macht keinen Sinn, dann schreibe lieber Cells(1,1), Cells(2,1) usw.
Zur leichteren Übersicht wenn man mit Cells() arbeitet laesst sich der Spaltenkopf über
-Optionen- zum Programmieren auf Z1S1 umschalten. Dann sieht man die Spalte als Zahl

Gruss Adis


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Barbara
Geschrieben am: 31.07.2014 18:27:22

Hallo Adis , Michael und alle anderen,
wozu ich die Namen verwende? - Um sie in ein Word-Dokument einzubauen.

Dort habe ich bereits ein funktionierendes Makro, das alle Textmarken mit dem Inhalt gleichnamiger Zellen einer Excel-Datei füllt. Diese Werte sind im Word-Dokument im Text verteilt, stehen in Excel aber in einer Tabelle.

Jetzt muss ich bei jeder Änderung (Erweiterung oder Reduktion) dieser Tabelle die Namen händisch neu vergeben bzw. löschen. Nach einer Änderung in der Anfangszeile müssen einige Namen geändert werden.

Hier https://www.herber.de/bbs/user/91818.xlsm
meine Excel-Tabelle mit genau angepasstem Makro.

Und hier nochmals das Makro:
Option Explicit

Sub ErstelleNamen()
Dim a As Variant

Dim Rng As Range, C As Range
Set Rng = Range("C3:G7")
For Each C In Rng
    C.Name = Cells(C.Row, 3) & "_" & Cells(3, C.Column)
    a = Cells(C.Row, 1)
Next
Set Rng = Nothing
End Sub
Die automatischen Namensvergabe würde mir sehr helfen.

LG, Barbara


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Barbara
Geschrieben am: 31.07.2014 20:02:57

Nachtrag die Frage ist noch immer offen
(habe vergessen, dies zu aktivieren)


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Adis
Geschrieben am: 31.07.2014 22:04:38

Hallo

ersetze die Zeile für C.Name durch diese Version. Die müsste laufen, ich habe sie gerade getestet.

C.Name = Chr(C.Column + 64) & "_" & C.Row

Der Unterschied liegt wohl darin, das ich zuerst die Spalte als Buchstabe angebe und nicht als Zahl.

Gruss Adis


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Michael
Geschrieben am: 01.08.2014 13:41:56

Liebe Barbara,

Du hast mich Nerven gekostet!

Kannst natürlich nichts dafür, daß die Geschichte gar nicht so simpel ist.

Zunächst bitte ich um Entschuldigung, daß ich Dich unterschätzt habe; Du hast mir im Gegenteil was voraus: Word-VBA, außerdem diese Tabelle in der Tabelle - auf so eine Idee käme ich gar nicht.

Ich habe ein bißchen recherchiert und herumprobiert und sende Dir anbei die Tabelle mit den Ergebnissen.

Ich ermittle mit einigen Formeln, ob Fehler zu erwarten sind: a) doppelte Zeilenbeschriftung, b) Zeilenbeschriftungen enthalten Leerzeichen und c) es ist gar keine Beschriftung vorhanden. Die stehen jetzt natürlich "mittendrin", aber es sollte kein Problem sein, die irgendwohin zu schaufeln, wo sie nicht stören. (Ich habe Matrixformeln eingesetzt; die {} werden nicht eingegeben, sondern von Excel erzeugt, wenn man das Editieren mit Strg-Shift-Enter abschließt).

Ich habe sie "bedingt formatiert", und Du könntest Dir überlegen, die Beschriftungen selbst bedingt zu formatieren oder eine Hilfsspalte zu verwenden, damit Du gleich siehst, wo die Fehler sind.

Die Werte habe ich im dazugehörigen Makro abgefragt und gebe sie nur aus - die Anpassung für Deinen Bedarf überlasse ich erst Mal Dir.

Ich habe die Funktionen auf Schaltflächen gelegt:
a) die Erzeugung der Namen, fast ungeändert eben Deine Routine
b) Anzeige der vorhandenen Namen unterhalb Deiner Tabelle
c) Löschen ALLER Namen

Spiel bitte mal damit herum...

Zur Logik: so weit ich das überblicke, werden die Namen ohne Bezug auf ein bestimmtes Tabellenblatt ausgegeben (bzw. intern gehandelt); das bedeutet, daß eventuelle Namen in anderen Blättern mitgelöscht werden, was unschön ist. Um das zu umgehen, könntest Du noch eine Konvention verwenden, die bei der Erzeugung der Namen etwa jedem Bereich die Nr. des Tabellenblattes voransetzt, die Du beim Löschen der Namen abfragst.

Ich hoffe, daß Du mit der Lösungsskizze was anfangen kannst, denn Du wolltest es ja schon bei der Eingabe "vollautomatisch" haben. Es ist halt ein Akt, alle Ereignisse abzufragen.

Mit dieser "halbautomatischen" Lösung sollte man ja auch arbeiten können, denn das Makro wird ja sicher händisch aktiviert, wenn die Eingaben gemacht wurden und übernommen werden sollen.

Oder andersherum: Du kannst ja die drei Fehlerwerte evtl. von Word aus abfragen und dann reagieren.

Also hier die Datei: https://www.herber.de/bbs/user/91828.xlsm

Happy Exceling,

Michael


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Barbara
Geschrieben am: 02.08.2014 01:00:21

Hallo Michael,

danke für Dein ausführliches Beispiel.

Eine Frage zu den Schaltflächen:
Wo kann man in der Datei sehen, welches Makro mit der Schaltfläche aufgerufen wird?

Ich habe nun ein Makro erstellt, das fast meinen Vorstellungen entspricht.

https://www.herber.de/bbs/user/91837.xlsm

Es ist mit "ErstelleNamen2()" bezeichnet und ist im Modul "Diese Arbeitsmappe" zu finden.

Jeder Name beginnt mit "B_".

Ganz leere Zellen werden bei der Überprüfung ignoriert.

Ein Fehler bei der Bezeichnung, der nicht aus Leerzeichen herrührt, wird angezeigt. Ich weiß zwar nicht, wann das sein kann, aber sicher ist sicher.

Wenn die Tabelle um eine Spalte erweitert wird, kommt automatisch eine Spaltenbezeichnung dazu. Also ganz leere Bezeichnungen wird es nicht geben.

Was noch zu machen wäre:
- Ein Button zum Aufrufen dieses Makros, positioniert in der Ecke links oben genau in die Zelle.

- Löschen aller Namen, die mit "B_" beginnen, bevor die neuen gesetzt werden.

- Die Messageboxen sind nicht sehr praktisch. Besser wäre vielleicht ein Fenster mit allen Meldungen, das aber stehen bleiben kann, wenn man die Korrekturen vornimmt und dann wieder verschwindet, wenn alles OK ist.

Kriegst Du das hin? Ich habe da zu wenig Kenntnisse in VBA.

Tabelle in Tabelle:
Die bekomme ich mit Einfügen - Tabelle. Finde ich sehr praktisch, weil sich der benannte Bereich von selbst anpasst und die Hintergrundmalerei nachgezogen wird, wenn ich die Ecke unten rechts verändere. Außerdem lässt sie sich einfach verschieben.

Das Makro im Word_Dokument:
Es fragt für jede vorkommende Textmarke nach, wie der Wert des gleichnamigen Zellinhalts in der Excel-Datei ist und setzt ihn in die Textmarke. Jede Textmarke kann nur einmal vorkommen. Und ein Name in Excel kann auch nicht doppelt vorkommen.

LB,
Barbara


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Michael
Geschrieben am: 02.08.2014 18:50:27

Hallo Barbara,

das mit den Schaltfälchen ist ganz einfach: Du klickst sie mit der rechten Maustaste an, und im Kontextmenü siehst Du die Zeile "Makro zuweisen". Wenn Du wiederum die anklickst, siehst Du, welches Makro gerade zugewiesen ist, und welche zur Verfügung stehen.

Es ist eine gute Sache, im Makro selbst direkt unter dem s*u*b den Namen der aufrufenden Schaltfläche(n) (und in wo sie sich befindet(n)) zu kommentieren.

Ich muß Dich für heute leider enttäuschen, da ich die nächsten 3 Tage bzw. Nächte arbeite und zwischendrin nicht allzu viel Zeit habe.

Naja, ich schau mal rein, und wenn ich's auf die Schnelle hinbekomme, bekommst Du die geänderte Datei gleich.

Schöne Grüße derweil,

Michael


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Barbara
Geschrieben am: 02.08.2014 19:13:53

"das mit den Schaltflächen ist ganz einfach:"
Jaja, so einfach, dass ich das übersehen habe. Danke für Deine Erklärung.

Alles Gute bei Deiner Arbeit.

LG,
Barbara


  

Betrifft: Immer die Hektik von: Michael
Geschrieben am: 03.08.2014 18:48:17

Hallo Barbara,

ich bin ein Rindvieh! Da hab ich mir die Tabelle schon angeschaut, aber vergessen, sie hochzuladen.

Damit werden meine gestrigen, weiteren Kommentare wohl verständlicher.

Ach, was soll's, ich mach jetzt noch schnell ...

... das, was ich mir vorgestellt habe: eine Zeile mit Fehlern bei Leerzeichen und 3 Spalten für die Arten von Fehlern links neben der Tabelle.

Was zu tun bleibt, ist quasi eine Initialisierung der Fehleranzeige, bevor Du mit der Namensvergabe beginnst: nämlich die Formeln runterkopieren, falls Zeilen eingefügt wurden.

Bekommst Du das selbst hin?

Schöne Grüße,

Michael

P.S.: Also nun doch MIT Datei: https://www.herber.de/bbs/user/91860.xlsm


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Michael
Geschrieben am: 05.08.2014 16:44:56

Hallo Barbara,

hast Du meine Antwort gesehen ("Immer die Hektik") bzw. die Datei angesehen?

Schöne Grüße,

Michael


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Barbara
Geschrieben am: 05.08.2014 23:16:01

habe ich offensichtlich übersehen.

Danke für den Wink.

Und danke für Dein Beispiel. Tolle Prüffunktionen.

LG,
Barbara


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Michael
Geschrieben am: 06.08.2014 11:24:55

na, das wollte ich hören.

LG,

Michael


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Barbara
Geschrieben am: 06.08.2014 17:40:09

Noch einige Fragen:

Wie kann ich prüfen, ob der Name "Banane" existiert oder nicht?

Ich mache das mit folgenden Zeilen:

Sub Name_Da()
'Erstellt 6.8.2014 von Barbara
Dim r As Range
On Error Resume Next
    Set r = Range("Banane")        'Fehlermeldung wird provoziert
        If Err = 0 Then             'Kein Fehler
            MsgBox "Banane eistiert bereits. "
        Else                        'Fehler ist entstanden
            MsgBox "Banane eistiert nicht. "
        End If
End Sub
Dazu brauche ich einen eigenen Range und muss die Fehler-Funktion in Anspruch nehmen-

Geht das irgend wie einfacher?

Noch eine Frage zu diesem Code:
Wie zu sehen ist, sind einige Zellen eingerückt, obwohl sie weiter links stehen sollten.
Also: In jeder Zeile eine Korrektur vornehmen. Gibt es da vielleicht irgend einen Trick, das mit einem Schlag zu erledigen? Oder eine Anzahl aufeinander folgender Zeilen einrücken?

LG,
Barbara


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Michael
Geschrieben am: 06.08.2014 20:49:33

Hi Barbara,

schau Dir doch mal meine s*u*b Namen_Anzeigen() an, in der letzten Datei im Modul1. Hier werden ALLE vergebenen Namen der EXCEL-Datei der Reihe nach "ausgedruckt."

Das Ding könnte man ja so ändern:

Function Name_da(ByVal sName As String) As Boolean
Dim nms As Names, r As Long, nd As Boolean

Set nms = ActiveWorkbook.Names

With Worksheets(1)
r = 1
nd = False

 While r <= nms.Count
   If nms(r).Name = sName Then
     nd = True
     r = nms.Count
    Else
     r = r + 1
   End If
 Wend
End With
Name_da = nd
End Function

Sub namenTesten()
 MsgBox Name_da("BBanane")
 MsgBox Name_da("Bereich")
End Sub
Ich hatte vor ein paar Tagen ein bißchen mit der "Tabelle in der Tabelle" herumgespielt, und es ist mir nicht gelungen, einen Spaltennamen doppelt einzugeben - in der Kopie wurde automatisch eine Ziffer angehängt.

Also wozu die Abfrage?

Sag, wie viele Daten hast Du eigentlich, wenn das Ding fertig ist?

LG,

Michael

P.S.: Lies mal http://support.microsoft.com/kb/843621/de
und vor allem http://www.office-loesung.de/ftopic191315_0_0_asc.php
--- Die "Bearbeiten-Symbolleiste"??? Wie immer in Office rechte Maustaste ins "Leere" neben vorhandener Symbolleiste, "bearbeiten" auswählen, und schon hat man Einrücken, Auskommentieren usw. verfügbar.


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Barbara
Geschrieben am: 06.08.2014 21:45:52

Oh, Tab geht auch für mehrere Zeilen. So einfach, dass ich gar nicht auf die Idee käme, daran zu denken. Danke.

Nicht in der Spalte, sondern in der Zeile können Doppelnamen entstehen.

Ich habe ungefähr 20 Zeilen und 5 Spalten.
Ich dachte nur, dass das vielleicht ohne Schleife ginge.


Aber meine Sache läuft ja bereits. Danke für Deine Hilfe.

LG,
Babara


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Michael
Geschrieben am: 08.08.2014 14:34:15

Hallo Barbara,

ich habe irgendwie das Gefühl, wir haben uns ein bißchen verzettelt.

Die Datei 91860.xlsm, die ich in meinem post vom 3.8. hochgeladen habe, gehört mit dem Text vom 02.08.2014 19:29:54 zusammen, nur daß in dem Text Sachen als "to do" bezeichnet waren, die ich in der Datei dann doch bereits umgesetzt hatte.

So gibt es keine Werte mehr in Spalte K, aber dafür alles, was Du brauchst, in den Spalten B-D. Hier werden pro Zeile/Zeilenkopf alle abgefragten Fehler angezeigt, und Du brauchst in Deinen Makros nur diese Werte abgreifen.

Ob man diese drei "Fehlerspalten" so (oder so ähnlich) stehen läßt, ist Geschmackssache - man könnte sie bedingt formatieren a la Ampel, man könnte sie, wenn sie nerven, auch ausblenden.

Andererseits: bei 5x20 hat der Anwender Deiner Tabelle alle Fehler sofort im Auge, wenn sie wie jetzt z.B. in rot angezeigt werden, und er kann sofort darauf reagieren.

Ich kann nicht nachvollziehen, ob Du diese Spalten nun übernommen hast oder nicht.

Ist ja auch egal, wenn es so läuft, wie Du es haben willst.

Vielen Dank für Deine Frage(n), es war nett mit Dir.

Happy Exceling,


Michael


  

Betrifft: AW: Jeder Zelle einer Tabelle automatisch Namen geben von: Michael
Geschrieben am: 02.08.2014 19:29:54

Hallo Barbara,

ich zitiere:

--Was noch zu machen wäre:
- Ein Button zum Aufrufen dieses Makros, positioniert in der Ecke links oben genau in die Zelle.

Done. Geht so: Schaltfläche m. rechter Maustaste anklicken, ESC-Taste (dann verschwindet das Kontextmenü, das Objekt bleibt jedoch im Fokus, und Du kannst die Schaltfläche herumschieben, wie Du möchstest, vergrößern, verkleinern usw.

- Löschen aller Namen, die mit "B_" beginnen, bevor die neuen gesetzt werden.

Done. S*U*B Namen_mit_B_Loeschen() in Modul1. Das Ding wird innerhalb Deines Codes aufgerufen.


- Die Messageboxen sind nicht sehr praktisch. Besser wäre vielleicht ein Fenster mit allen Meldungen, das aber stehen bleiben kann, wenn man die Korrekturen vornimmt und dann wieder verschwindet, wenn alles OK ist.

Naja, die Informationen stehen doch in Spalte K: Zeilen mit Doppelten sind farblich hervorgehoben. Letztlich kannst Du diese Spalte (vielleicht mit einer leeren Spalte dazwischen) direkt neben Deine Tabelle setzen.

Und in Zelle K2 hast Du direkt die Information verfügbar, ob Doppelte vorhanden sind - ich habe in Deinen Code eine entsprechende MsgBox eingefügt.

Sagen wir so: wenn Du alle diese errechneten Fehlerwerte optisch neben Deine Tabelle setzt, hat der Anwender sie im Blick und kann bei der Eingabe darauf reagieren....

Ach so, und wenn Du die nicht sehen willst, machst Du ne bedingte Formatierung, damit alles weiß auf weiß ist, wenn alles ok ist. Nee, mach das nicht: Du mußt zumindest die Hilfsspalte mit den Doppelten wieder auffüllen, wenn Du die Tabelle erweiterst.

So, ich muß jetzt los.

Schöne Grüße,

Michael


 

Beiträge aus den Excel-Beispielen zum Thema "Jeder Zelle einer Tabelle automatisch Namen geben"