Office: Zahlenformat per VBA ändern

Hallo liebe Leute,

auch von mir noch ein verspätetest Frohes Neues!

Ich möchte in neu erstellten Pivottallen im Datenbereich einfach irgend ein Feld anklicken und dann per Makro das Zahlenformat ändern. Standardmäßig zeigt Excel zunächt z.B. 1234567,89 an. Das will ich als 1.234.567 sehen.

Das ständige rechte Maus / Feld / Zahlen --> Zahl, Dezimalstelle 0, Flag bei 1000er Trennzeichen (.) ist mühsam auf die Dauer.

Die Durchsuchung alter Beiträge brachte noch nicht das gewünschte Ergebnis. Ein VBA Beispiel fand ich hier:
http://www.ms-office-forum.net/forum...t=Pivottabelle

mit dem Code:

Code:

 

Moin, Woody,

schöner wäre es gewesen, Du hättest den Suchbegriff aus dem Link entfernt und vielleicht auch den kompletten Code zitiert - aber ich habe das Vorgehen trotzdem wiedergefunden...

Die erste Möglichlkeit wäre natürlich das Anschmeißen des Makro-Rekorders gewesen - da ich nicht weiß, wie Deine Pivot-Tabelle heißt, muß dies angepasst werden (bei mir läuft es unter Excel97): Code:

 

Hi jinx,

danke mal wieder für eine Antwort. Ich kommen erst heute dazu darauf zu antworten.

Der Name war wohl bei mir ein Problem, den ich aber dank deines Beispiels ("Pivot-Tabelle5") ändern konnte (benötige nur ..Tabelle1").

Damit funktioniert schon einmal überhaupt eine Änderung des Zahlenformats. Super! Leider wollte ich nur die Werte in den Datenfeld anfassen und nicht auch die Werte in den Zeilenkopf (analog Spaltenkopf). Schlau wie ich dachte, habe ich den Befehlesteil "xlDataAndLabel" gekürzt auf "xlData" was aber leider nicht so recht funktionierte.

Hast du noch einen Tipp für mich? Gibt es doch noch eine Möglichkeit

Schönen Gruß in die northern hemisphere

Woody

 

Moin, Woody,

dann solltest Du den Befehl xlDataOnly nutzen, der das Format nur für den Bereich der Daten einstellt.

 

Hallo jiinx,

die Emailbenachrichtigungsfunktion ermöglicht einem ja schnelle Reaktionszeiten in diesem Forum! Toll. Danke für die prompte Antwort.

Deine Lösung sieht bestechend und einleuchtend aus, klappt aber irgendwie nicht. Ich erhalte einen Laufzeitfehler 1004 " Die PivotTables-Eigenschaft des Worksheet-Objektes kann nicht zugeordnet werden". Der mögliche Fehler eines anderen Blattnamens scheidet aus. :-(

Gruß
Woody

 

Moin, Woody,

wenn man denn die Postabholzeiten auf ein moderates Maß eingestellt hat, ja.

Zur Fehlermeldung: Ich kann diesen Fehler unter XL8 simulieren, wenn ich mich auf einem anderen Blatt befinde, auf dem es die angesprochene Pivot-Tabelle nicht gibt, und dann das Makro aufrufe. Und da ActiveSheet immer von der gerade aktuellen Tabelle ausgeht, solltest Du eventuell den Namen der Tabelle explizit mit Worksheets("Name_Deiner_Tabelle") ansprechen...

 

Du, ich habe es eben einfach noch einmal probiert, alles frisch von vorne begonnen ... und es klappte! Was auch immer vorher nicht ging, I´ll never know.
Dein Tipp mit dem anderen Blatt ist gut. Ist mir aber nicht unterlaufen. Seis drum, vielen Dank für deine Mühe das noch einmal zu testen/simulieren.

Ich freue mich jedenfalls sehr über mein neues kleines und feines Makro, welches mir viel Freude bereiten wird. *happy

Schönen Abend
Woody

 

Hallo jiinx ... oder alle Anderen,

ich muss doch noch einmal meine schon als erledigt geglaubte Anfrage wieder aufnehmen, nachdem ich einige Erfahrung nun gesammelt habe mit dem Makro:

Sub PivotFormatieren()
ActiveSheet.PivotTables("Pivot-Tabelle1").PivotSelect "", xlDataOnly
Selection.NumberFormat = "#,##0.00"
End Sub

Frage:
Kann man den Teil "Pivot-Tabelle1" vielleicht variabel gestalten? Es kommt doch zu häufig vor bei mir vor, dass der Name nicht passt ("..2" oder "..3" usw.). Folgender Versuch von mir hat nicht geklappt. Kann mir jemand die richtige Syntax geben?

Sub PivotFormatieren()
Dim blatt As String
blatt = ActiveSheet.PivotTables.Name '

 

Moin, Woody,

was genau verstehst Du unter variabel, meinst Du etwas in der Richtung

Code:

 

Moin jinx, sei gerüßt!

Sorry, ich habe mich wohl nicht verständlich ausgedrückt. Das erste Makro ist wohl nicht das was ich suche.

Bei der Erstellung einer Pivottabelle wird nicht immer der gleiche Name vergeben (offenbar abhängig davon, ob man schon vorher eine andere Pivottabelle erstellt hat in einer einzigen Datei), weshalb ein starrer Name in einem Makro leider nicht so ideal ist.
Deshalb: kann man diesen Namen nicht irgendwie automatisch auslesen und dann verwenden?
Dein zweites Makro hat zwar seinen eigenen Charme, aber eine Eingabe dieser Art ist genauso umständlich wie das normale formatieren von Hand, also keine Erleichterung :-(

Hhm, .. war das verständlich? So einfach scheint es offenbar nicht zu sein.

Schönen Gruß .... und ich bange und hoffe.

Woody

 

Moin, Woody,

das kann ich in der Tat nicht verstehen, weil beim ersten Makro lediglich das Tabellenblatt per Name angegeben wird - der Name der Pivottabelle ist egal, da sie über die Reihenfolge des Anlegens angesprochen und markiert werden, und zwar nacheinander alle auf dem Blatt vorhandenen. Wenn dies nicht ausreicht, solltest Du mir eine Mappe als Beispiel übersenden oder zum Download hier im Forum zur Verfügung stellen.

 

Hallo jiinx,

ich bin es mal wieder. Vielleicht geht es doch ohne Dateiupload. Ich glaube ich habe mich noch nicht verständlich ausgedrückt. Sorry.

Also, ich möchte generell nach Erstellung einer Pivottabelle (das können mehrere in einer Datei sein, auch auf verschiedenen Blättern) mit meinem Cursor ein Datenfeld der gewünschten Pivottabelle anklicken und dann einen Button zum Formatieren anklicken (genauso als ob ich z.B. eine x-beliebige Zelle per Button Fett oder linksbündig formatieren würde). Dein erstes Makro geht genau in die richtige Richtung:

Sub Makro7()
ActiveSheet.PivotTables("Pivot-Tabelle5").PivotSelect "", xlDataAndLabel
Selection.NumberFormat = "#,##0.00"
End Sub

Der einzige Harken ist der Name der Pivottabelle (in deinem Makro "Pivot-Tabelle5"). Man kann ihn sehen mit einem rechten Mausklick / Optionen --> Pivot-Tabellenoptionen unter Name. Diese Name ist ja immer unterschiedlich, abhängig, welche Pivottabelle ich anwählt habe. Das ist der Teil, den ich variabel im Makro benötige, den wenn es nicht die "Pivot-Tabelle5" ist, bricht das Makro verständlicherweise ab.

Also zusammengefasst:

Ich möchte genauso wie man eine einzige Zelle umformatieren kann (z.B. in Schriftart fett) und hierfür einen Button klickt, dieses machen für ausgewählte Pivottabellen (allerdings nur um das Zahlenformat zu ändern, was bei mir sehr häufig vorkommt)

Ich möchte nicht: alle Pivottabellen einer Datei umformatieren und auch nicht umständlich den Namen eingeben müssen. Das wäre genauso aufwendig wie von Hand zu formieren á la rechte Maus / Feld / Zahlen --> Zahl, Dezimalstelle 0, Flag bei 1000er Trennzeichen (.)

Entschuldige, wenn ich nerve. Ich hoffe du oder ein andere antwortet mir noch ;-)

Schönen Gruß

Woody

 

Hallo,

versuch doch mal, folgendes Makro einer Schaltfläche zuzuordnet. Per Intersect wird geprüft, ob und ggfs innerhalb welcher Pivottabelle sich der Cursor befindet. Das Zahlenformat dieser Tabelle (und nur dieser) wird geändert. Evtl. musst Du noch den Parameter xlDataAndLabel anpassen. Das war in der bisherigen Diskussion nicht eindeutig.

Viel Erfolg

Johannes

Sub PivotTabelleZahlenFormat()
For Each pvtbl In ActiveSheet.PivotTables
Set isect = Intersect(pvtbl.TableRange1, ActiveCell)
If Not isect Is Nothing Then
pvtbl.PivotSelect "", xlDataAndLabel
Selection.NumberFormat = "#,##0.00"
End If
Next
End Sub

 

Genial !!! *happy
Genial !!! *happy
Genial !!! *happy

Das ist exakt DAS was ich täglich x-fach brauche!

Suuuper vielen Dank JFreudens! Ich könnt´ dich *knuddel .....

Mit dem xlData hattest du natürlich Recht. Ich wollte aber diese Anfrage nicht noch weiter verkomplizieren, als sie offenbar schon war (zumal ich dieses Detail selbst lösen konnte mittlerweile, dank jiinx wohlgemerkt). Deinem aufmerksamen Expertenauge ist dieses aber nicht entgangen! Vielen Dank, dass du dir die Mühe gemacht hast alle diese Beiträge durchzulesen und dir die Zeit für eine Lösung zu nehmen!!!

Zur Sicherheit nun das finale Makro, welches ich ab heute verwenden werde (Zahlenformat mit Tsd. Punkt und keiner Nachkommastellen):

Sub PivotTabelleZahlenFormat()
'by JFreudens
For Each pvtbl In ActiveSheet.PivotTables
Set isect = Intersect(pvtbl.TableRange1, ActiveCell)
If Not isect Is Nothing Then
pvtbl.PivotSelect "", xlDataOnly
Selection.NumberFormat = "#,##0"
End If
Next
End Sub

Es grüßt ein glücklicher Woody *bye *yelrotflmao