Office: Zellen färben mit VBA - gelöst

Hi Achim,

im konkreten Fall ist es so, dass sich dieser Ausdruck auch ohne Punkte davor auf das Tabellenblatt wks2 in der Arbeitsmappe wkb1 bezieht - setze mal eine Überwachung auf Range(Rows(7), Rows(letzteZeile)) und schaue dann unter Parent -> Parent und dort Name nach. Verstehe einer Excel... ;-)

Bis später,
Karin

 

Hallo Karin

Also eine Überwachung "setzen" habe ich im VBA-Editor gefunden.
Was ich dann damit machen soll/anfangen kann, habe ich allerdings keine Ahnung. (Parent wurde mir dort auch nirgendwo angezeigt)
Hatte noch nicht die Zeit danach zu googeln.
(habe mit den neuen Erkenntnissen nach fehlerhaften/unvollständigen Referenzierungen gefahndet - iss mir schlecht... was ich da so alles gefunden hab - immerhin weiß ich ja wer es verbockt hat) ;O)


Was "Parent" in Excel bedeutet habe ich hier http://www.online-excel.de/excel/singsel_vba.php?f=131 sehr schön erklärt bekommen.
Und mit

Code:

MsgBox Range(Rows(7), Rows(letzteZeile)).Parent.Name

bekomme ich auch die entsprechende Antwort.


Ganz lieben Dank für Deine Erklärungen.
Heute habe ich einiges gelernt.

 

Hi Achim,

wenn du im VBA-Editor eine Überwachung auf den genannten Codeausdruck gemacht hast, wird dir unterhalb des Codefensters das Überwachungsfenster angezeigt, in dem du eine Baumstruktur siehst, die alle Eigenschaften umfasst, die zu dem überwachten Ausdruck gehören. In dieser Baumstruktur gehst du auf den Eintrag Parent (bzw. auf das + vor dem Eintrag) - dann öffnet sich eine Unterstruktur, wo du noch einmal auf das + vor Parent klickst - alles ähnlich wie im Windows- oder im Projektexplorer. Dann scrollst du nach unten bis zur Eigenschaft "Name" und rechts wird dir dann einiges dazu angezeigt.

Mit deiner Codezeile bekommst du den Namen des Tabellenblattes angezeigt - wenn du jedoch .Parent.Parent.Name schreibst wird dir die Arbeitsmappe genannt, in welcher sich der betreffende Bereich befindet. Im Prinzip ist das nichts anders als wie du im Überwachungsfenster vorgehst.

Bis später,
Karin

 

Hallo Karin,

Ich hoffe ich überstrapaziere Deine Geduld nicht...
aber ich finde keine Baumstruktur...

 

Yup, denn... Eigentlich hat Beverly es schon erklärt, aber ich versuch's mal mit anderen Worten...

Diese ganzen "Range"-Dinger haben eigentlich immer einen "default"-Bezug auf ein Arbeitsblatt, wenn nichts weiter angegeben ist. Ein

Code:

range("a1")

bezieht sich per default, also wenn nicht explizit was anderes angegeben hat, auf das "aktive" worksheet und ist damit gleichbedeutend mit

Code:

activesheet.range("a1")

Das "Worksheet" wiederum hat ebenfalls einen "default"-Bezug, nämlich auf die Arbeitsmappe, und zwar die, in der der VBA-Code gerade ausgeführt wird. (Achtung: Das ist nicht zwingend auch die "aktive", also die mit Fokus auf dem Fenster!) Damit wäre es gleichbedeutend mit

Code:

thisworkbook.activesheet.range("a1")

Solange man nicht mit mehreren Arbeitsmappen parallel geöffnet hantiert, wird man auch nicht explizit die Referenz darauf setzen. Und wenn man im Code, z.B. nach einem Button-Klick, nicht auf andere Blätter wechselt, kann man auch gerne diese Referenz weglassen und die 1. Schreibweise benutzen.

Aber stell Dir vor, Du hast ein Blatt mit einem Button. Wird der angeklickt, wird auf ein weiteres Blatt gewechselt, um dort eine Liste abzuarbeiten. Irgendwelche Ergebnisse (Summen oder was auch immer) sollen dann auf dem Blatt zusammengefaßt werden, wo der Button liegt.

Also könnte es grob so aussehen:

Code:

[click]
worksheets("tbl2").activate
range("a2").activate
  [irgendwas in xyz berechnen]
worksheets(tbl1").range("b2").value = xyz
[...]
worksheets("tbl1").activate

Das "range" bezieht sich auf das "aktive" Blatt und "tbl2" wurde am Anfang aktiviert. Daraus ergibt sich, daß ich "tbl1" explizit angeben muß, um das Ergebnis auf das nicht-aktive Blatt zu schreiben.

Um das jetzt mit "with" in der Schreibweise zu verkürzen:

Code:

[click]
with worksheets("tbl1")
  worksheets("tbl2").activate
  range("a2").activate
    [irgendwas in xyz berechnen]
  .range("b2").value = xyz
  [...]
  .activate
end with

[edit: Das "end with" war mir ein bißchen zu hoch gerutscht.]
Beachte den Unterschied bei "range" und "activate": Ohne . wird das aktive Blatt "tbl2" benutzt, mit . aber wird der Teil davor mit dem "Verkürzer" aus der "with"-Anweisung erweitert.

Und gerade weil so ein kleiner, vermaledeiter . einem den ganzen Samstag verhageln kann, benutze ich lieber grundsätzlich, immer, überall und konsequent meistens ;-) folgendes Konstrukt:

Code:

[click]
dim s1 as worksheet
dim s2 as worksheet
set s1 = worksheets("tbl1")
set s2 = worksheets("tbl2")
s2.activate
s2.range("a2").activate
  [irgendwas in xyz berechnen]
s1.range("b2").value = xyz
[...]
s1.activate

Ich verkürze die Schreibweise also nicht mit "with", bei dem man spätestens in 2. Verschachtelung fast unweigerlich ins Schwimmen kommt, sondern bastel mir mit "set" quasi Hilfsvariablen in kurzer Schreibweise, die ich grundsätzlich benutze.

Das hat auch den Vorteil, daß ich "activate"-Zeilen jederzeit auskommentieren kann, ohne befürchten zu müssen, daß dann Werte vom falschen Blatt gelesen oder ins falsche Blatt geschrieben werden. Ich verlasse mich also nicht darauf, daß das "activesheet" auch das ist, was ich hoffe.

Zur Not ließe sich das aber auch immer noch mit "with" kombinieren, wenn man denn leicht masochistisch veranlagt ist:

Code:

[click]
dim s1 as worksheet
dim s2 as worksheet
set s1 = worksheets("tbl1")
set s2 = worksheets("tbl2")
with s1
  s2.activate
  range("a2").activate
    [irgendwas in xyz berechnen]
  .range("b2").value = xyz
  [...]
  .activate
end with

Dein

Code:

SetRange Range(Rows(7), Rows(letzteZeile))

bezieht sein "range" also auf das aktive Arbeitsblatt. Soweit ok, denke ich, denn Du willst ja was von Zeile 7 bis y auf dem Blatt haben.

Setzt Du einen . vor das range, wird das Objekt aus der "with"-Anweisung genommen:

Code:

With .Sort

Das wiederum hat selbst einen . vorweg und wird entsprechend mit dem vorigen "with" ergänzt:

Code:

With Workbooks(wkb1).Worksheets(wks2)

Womit kpl. dort steht:

Code:

With Workbooks(wkb1).Worksheets(wks2).Sort

Mit dem . vor z.B. "rows()" bezieht sich die Zeilen-Angabe dann nicht mehr auf das Objekt "activesheet" in "thisworkbook", sondern auf das Objekt "sort", welches sich auf dem Blatt "wks2" in der Mappe "wkb1" befinden soll. Und das ist dann der Punkt, an dem Excel gnatzig wird.

Gruß, Michael

 

Hallo Michael,

ganz lieben Dank für die sehr detaillierte und ausführliche Erklärung.


Das ".range" auf das ".sort" zu beziehen, macht natürlich nicht wirklich Sinn.
Genau den Teil hatte ich nicht bedacht.
Womit wir beim verschachtelten "with" wären...
Wie Du ja bereits sagtest:

......................................................

Okay, das habe ich verstanden, das ist nachvollziehbar.

Wenn ich mir meinen konkreten Code so anschaue, verwundert es mich dann allerdings, dass er funktioniert, denn:
das Blatt auf das sich

Code:

Range(Rows(7), Rows(letzteZeile))

bezieht, habe ich nicht aktiviert.
Ich habe den Code rückwärts verfolgt um sicher zu gehen.
Einzig über "with" wird auf das Blatt verwiesen wo ich sortiere.
"activate" vermeide ich, wenn möglich.
Scheint ja wohl auch für so einiges nicht notwendig zu sein, denn Zellen beschreiben, formatieren, sortieren, Blatt/Mappe schützen und "entschützen" etc. geht alles ohne "activate".
(und natürlich ohne "select" - *pfui!*) ;O)


......................................................

Das ist ein sehr guter Punkt.
Würde ich auch gerne exzessiv nutzen.
Denn durch das Referenzieren sind bei mir viele Zeilen "unschön" lang.
Das ist unpraktisch beim Schreiben/Pflegen/Fehlersuchen/Lesen.
(vor allem auf meinem kleinen Monitor, in der Firma gehts besser, aber da habe ich so selten die Zeit...)

Bisher arbeite ich mit strings, aber das ist halt immer noch länger als mir lieb ist:

Code:

Workbooks(wkb2).Worksheets(tbName)

Deine Methode habe ich vor längerem schon mal ausprobiert und hatte Probleme damit.
Jetzt habe ich wieder damit rumprobiert, und bekam wieder Probleme.
Eine Mappe oder ein Blatt zu definieren und den Schutz aufzuheben/ zu setzen, Zellen zu beschreiben usw. scheint zu klappen.
Aber Objekte auf einem Blatt anzusprechen (CommandButton etc.), verweigert Excel.

Code:

ws1.CommandButton13_AendSpeich.BackColor = &H80FF80

Dann kennt es plötzlich die Methode nicht ???
Welche Regeln gibt es denn da?
(vielleicht ein Link, irgendwo wird sowas ja beschrieben sein...)

Im Zweifelsfall nehme ich halt die langen Zeilen in Kauf, denn ordentliches Referenzieren ist bei mir wichtig, denke ich.
Ich habe mehrere recht umfangreiche Projekte, die manchmal auch gleichzeitig geöffnet sein können.
Allerdings "arbeitet" immer nur eins... das/die andere/n sind halt einfach nur offen.
Aber ich springe sehr viel zwischen Blättern, und bei dem konkreten Projekt auch zwischen zwei Mappen (mit noch mehr Blättern...).

 

Das ist in der Tat merkwürdig, aber Beverly schrieb ja auch schon:

Vielleicht ist das eine Eigenart in x2007? Mein x2000 referenziert da eigentlich richtig und nachvollziehbar. Zumindest hab ich es noch nicht so nachbauen können, daß es "merkwürdig" referenziert.

In der Online-Hilfe zur Range-Eigenschaft steht unter "Anmerkungen" dann auch im ersten Absatz:

(Ich verstehe das "mit einem" im ersten Satz aber nicht so ganz. Eigentlich müßte da ein "mit keinem" bzw. "ohne einen" stehen...)

Und: ich scheine mich geirrt zu haben, was die Default-Referenz für die Blätter sind, denn unter der Sheets-Eigenschaft findet man unter "Anmerkungen":

Also Essig mit thisworkbook, replaced by activeworkbook. ;-) Damit ist es genau umgekhrt: Es ist die Mappe, die gerade den Windows-Fokus hat, aber nicht notwendiger die Mappe, in der sich der gerade ablaufende Code befindet. sorry...

Und das ist auch wieder ein Punkt, weswegen ich die set-Geschichte bevorzuge. Da setze ich die Referenz explizit einmal am Anfang der Routine und muß mich später um diese Feinheiten nicht mehr kümmern. Vor allem muß ich mich nicht darum kümmern, wenn in unterschiedlichen Versionen unterschiedliche Defaults gesetzt sind.

Code:

dim wkb1 as workbook
dim sht1 as worksheet
dim rng1 as range

const blattname = "auswertung"

set wkb1 = thisworkbook
set sht1 = wkb1.worksheets(blattname)
set rng1=sht1.range("c3:e5")

wkb1.activate
sht1.activate
rng1.copy destination:=[...]

Gruß, Michael

 

Diese "Controls" anzusprechen ist tricky, denn es sind keine "Controls" im herkömmlichen Sinne, sondern vielmehr "OLE-Objekte"

Wenn Du so ein Objekt, sagen wir mal einen "CommandButton1", auf das Blatt setzt, achte mal auf folgende Eigenschaften:
a) links oben, wo sonst auch die aktive Zelle steht, steht der Name des Buttons, wenn er im Entwurfsmodus angeklickt wird.
b) rechte Taste auf dem Button, dann "Eigenschaften" - dort steht neben "Name" ebenfalls der Name des Buttons.
c) und in den Eigenschaften gibt's auch noch "Caption", also die Beschriftung des Buttons.
d) egal, welche dieser Eigenschaften Du änderst, eins bleibt die ganze Zeit gleich: In der Bearbeitungszeile, wo man sonst auch die Formeln in den Zellen bearbeiten kann, steht immer

Code:

=EINBETTEN("Forms.CommandButton.1";"")

Ansprechen kann man diesen Button grundsätzlich auf 2 verschiedenen Wegen. Vielleicht gibt's noch ein paar mehr, aber ich benutze eigentlich immer einen dieser:

a) im Source direkt den Namen des Buttons benutzen:

Code:

Private Sub CommandButton1_Click()
    Dim btn1 As Object
    Set btn1 = CommandButton1
    MsgBox btn1.Name
End Sub

b) über die OLE-Objekt-Auflistung mit dem Namen als Index:

Code:

Private Sub CommandButton2_Click()
    Dim sht1 As Worksheet
    Dim btn2 As Object
    Set sht1 = ActiveSheet
    Set btn2 = sht1.OLEObjects("CommandButton2").Object
    MsgBox btn2.Caption
End Sub

Bei diesen beiden Methoden gibt es durchaus Unterschiede. Ich hab mich nie drum gekümmert, warum das jeweils so ist, ich hab's einfach akzeptiert. Z.B. gibt es bei der 2. Methode keine "Name"-Eigenschaft des Objekts, weswegen ich im o.a. Beispiel stattdessen die "Caption" ausgebe.

Auch kannst Du bei der ersten Methode nicht noch explizit den Blattnamen benutzen. Was also nicht geht:

Code:

Private Sub CommandButton1_Click()
    Dim sht1 As Worksheet
    Dim btn1 As Object
    Set sht1 = ActiveSheet
    Set btn1 = sht1.CommandButton1
    MsgBox btn1.Name
End Sub

"CommandButton1" ist keine Methode und kein Datenobjekt von "ActiveSheet". Es ist aber direkt an das Blatt gebunden, denn Du kannst einen "CommandButton1" sowohl auf "Tabelle1", wie auch auf "Tabelle2" haben.

Die Eigenschaften, die Du ansprechen kannst, kannst Du recht simpel sichtbar machen:
a) setze einen Haltepunkt jeweils auf der Zeile mit der "msgbox".
b) lass Dir das "Ansicht - Lokalfenster" anzeigen.
c) beende den Entwurfsmodus in der Tabelle
d) drücke den Button 1 bzw. 2
Jetzt landest Du wegen des Haltepunkts im VBA-Editor und siehst am unteren Rand die lokalen Variablen. Links von z.B. "btn1", der mit "set" gesetzten Objekt-Variablen, das [+] anklicken und Du siehst die einzelnen Eigenschaften.

Gruß, Michael

 

Hallo Michael

Ich bin heute nicht ganz konzentrationsfähig, und habe gerade etwas Schwierigkeiten meine Hirnwindungen passend zu verbiegen. ;O)
Aber mir scheint, dann passt es doch (irgendwie).
Denn, beziehe ich mich auf ein Blatt der Mappe mit Code, reicht das Ansprechen über "with".
Beziehe ich mich auf ein Blatt einer anderen Mappe, muss ich diese und das entsprechende Blatt erst aktivieren, sonst gibts Fehler.

 

Nochmal Hallo... :O)

Okay, muss ich mir wohl mal ganz in Ruhe genauer anschauen,
und bis dahin halt die Buttons direkt ansprechen:

Code:

Workbooks(wkb1).Worksheets(wks1).CommandButton1

Da weiß ich wenigstens das es funktioniert.
Kommt ja eh nur in Frage wenn ich in einem Extra-Modul und nicht im Modul des Blattes bin. (Me.CommandButton1)

Aber echt schade das es mit der zweiten Methode nicht funktioniert.
Die Schreibweise wäre übersichtlich, konsequent und kurz.

Code:

Dim wbk1 As Workbook
Dim sht1 As Worksheet
Dim btn1 As Object
...
wbk1.sht1.btn1.(blabla)

Lieben Dank für Deine Geduld.
Aus einem "sorgenvoll-erdrückenden Problem" ist eine äußerst interessante und lehrreiche Unterhaltung geworden.
Zwar (unwesentlich) von Thema abgewichen, aber egal, der Input zählt. ;O)

 

Das Blöde ist, daß man bei den verschiedenen Methoden leicht auf unlogische Widersprüche und dann auch noch auf von der Online-Hilfe abweichendes Verhalten stößt.

es geht:

Code:

a)
set btn1=commandbutton1
b)
set btn2=activeworkbook.activesheet.commandbutton2

aber es geht nicht:

Code:

c)
set wkb3=activeworkbook
set sht3=wkb3.activesheet
set btn3=sht3.commandbutton3

obwohl das ja eigentlich die gleiche Syntax wie b) ist...

was wiederum geht:

Code:

d)
set btn4=OLEObjects("CommandButton4").Object
e)
set btn5=ActiveWorkbook.ActiveSheet.OLEObjects("CommandButton5").Object
f)
set wkb6=activeworkbook
set sht6=wkb6.activesheet
set btn6=sht6.OLEObjects("CommandButton6").Object

Obwohl das Objekt über die OLEObjects herausgesucht wird, darf z.B. btn4 aber nicht als "OLEObject", sondern muß als normales "Object" deklariert (DIM-Anweisung) werden.

Interessant auch, wenn man dann den Namen ändert. Also man setzt "CommandButton7" auf das Blatt und ändert den Namen dann in "btn_Button7" um. Laut Online-Hilfe muß man in der OLEObjects-Auflistung den ursprünglichen Namen benutzen. Tatsache aber ist, daß das nicht geht, sondern der aktuelle Name benutzt werden muß:

Code:

g)
set btn7=OLEObjects("btn_Button7").Object

Zitat aus der Online-Hilfe:

Irgendwie alles ein bißchen merkwürdig, wenn man genauer hinguckt. Aber wahrscheinlich gibt es für das alles eine sinnvolle Erklärung.

Gruß, Michael

 

Hallo Michael,

Rein logisch betrachtet müsste das Zitat aus der Online-Hilfe absoluter Humbug sein.

Bsp:
Ich setze mehrere CommandButtons auf ein Sheet/in eine UserForm.
Diese werden ja automatisch mit 1 bis x durchnummeriert.
Danach benenne ich die Buttons so wie es mir gefällt.
Später stelle ich fest, ich brauche noch mehr Buttons, und füge sie ein.
Diese werden jetzt wieder automatisch mit 1 bis x benannt, und ich benenne sie um.

Würde jetzt die Vorgehensweise aus der Hilfe stimmen, wie sollte Excel wissen ob ich den zu erst oder den als zweiten gesetzten CommandButton1 ansprechen will?

Mir scheint, Du bist ein sehr positiv denkender Mensch, der an das Gute in allem glaubt. *zwinker*
Bei Microsoft auf sinnvolle Erklärungen zu hoffen, habe ich schon lange aufgegeben. ;O)
Aber vielleicht bin ich auch nur einfach nicht Genie genug um MS zu verstehen.


Danke für Deine Beispieldatei.
Damit werde ich mal ein wenig spielen.

LG Achim