Change-Event nur bei Zelle mit benanntem Bereich

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Private Sub Worksheet_Change(ByVal Target As Range) Const relNam$ = "*JHoehe*" Dim obTNam As Name, relParent As Object On Error Resume Next If Target.Count = 1 Then If IsError(Target.Name) Then Set relParent = Me: GoSub ob If obTNam Is Nothing Then Set relParent = Me.Parent ob: For Each obTNam In relParent.Names If obTNam.Name Like relNam Then If Not Intersect(Target, obTNam.RefersToRange) Is Nothing Then Exit For End If Next obTNam If Not obTNam Is Nothing Then Call Ausfuehren ElseIf Me.Name = relParent.Name Then Return End If End If Else: Set obTNam = Target.Name If obTNam.Name Like relNam Then Call Ausfuehren End If Set obTNam = Nothing: Set relParent = Nothing End If End Sub

…Peter,
auf der Grundlage eines möglichen Szenarios:
Du hast gewünscht, dass das Ganze sowohl für einzelne benannte Zellen als auch ganze benannte ZellBereiche fktioniert, zumindest hatte ich dich so verstanden. Wenn nun noch echte VerbundZellen ins Spiel kommen, wdn diese wg If Target.Count = 1 Then nicht einbezogen. Um das auch noch zu gewährleisten, sind folgde Änderungen/Ergänzungen erforderlich:
Dim zAnz As Long, obTNam As Name, relParent As Object
On Error Resume Next
If Target.MergeCells Then zAnz = Target.Cells(1).MergeArea.Count Else zAnz = 1
If Target.Count = zAnz Then
Dadurch wdn diese miterfasst, wenn nur sie geändert wdn. Falls mehrere Zellen auf 1× geändert wdn (zB durch Aufkopieren von Werten), wird die Aktion nicht ausgelöst!, es sei denn, es handelt sich um nur 1 VerbundZelle ohne weitere (Verbund-)Zellen.
Dabei wdn benannte VerbundZellen, deren ZellBezug nur auf die 1.Zelle des Verbunds verweist, wie benannte ZellBereiche aus mehreren Zellen behandelt, da hier ebenfalls kein Name erkannt wdn kann. Das ist nur möglich, wenn alle Zellen des Verbunds im NamensBezug angegeben wurden. Diese wdn dann folglich wie einzelne benannte Zellen behandelt, d.h., der Zyklus muss nicht durchlaufen wdn.
In einem DokumentKlassenModul bezeichnet Me das Dokument, hier also das Blatt. Dieses steht in einer sog Child-Beziehung zu seinem Parent, also in diesem Fall der Mappe, der das Blatt zugehört. Me.Parent bezeichnet also das übergeordnete Workbook. Da das 2 verschiedene Objekte sind, ich aber ggf beide in nur einem Zyklus nacheinander verwenden wollte, musste relParent eingeführt und allgemein As Object deklariert wdn. Notwendig ist das, weil Namen sowohl auf Mappen- als auch BlattEbene definiert wdn können. So wdn auf jeden Fall beide Arten erfasst.
Set relParent = Me: GoSub ob ist eine Zeile, die 2 nacheinander auszuführende Befehle enthält, die durch den : getrennt wdn (Achtung! Ein : nach nur einem Wort am ZeilenAnfang macht dieses Wort zur SprungMarke → hier ist es ob!). Damit dürften beide Befehle in ihrer Bedeutung klar sein, wobei hinzuzufügen wäre, dass es sich bei GoSub um einen sog UnterPgm(-Ein-)Sprung handelt. Hier fungiert in diesem Fall der PgmTeil ab der Marke ob: als UP. Am Ende eines UPs muss angegeben wdn, wo die normale PgmAbarbeitung fortgesetzt wdn soll. Return bewirkt dann die Rückkehr zum dem UP-AufrufBefehl nächstfolgd Befehl.
Innerhalb des For Each-Zyklus (auch Schleife genannt) wird nacheinander der Name jedes NamensObjekts des Blattes (sind derartige Namen nicht vorhanden, wird der Zyklus nicht durchlaufen) mit der TextKonstante vgln. Entspricht er diesem Muster, muss nun noch überprüft wdn, ob die geänderte Zelle (Target) innerhalb des BereichsBezugs (RefersToRange) dieses Namens liegt (zu Fehlern könnte es hier kommen, wenn ein dem Muster entsprd Name keinen BereichsBezug hat, sondern eine Fml oder Konstante bezeichnet!). Im True-Fall wird der Zyklus verlassen. Dadurch behält das LaufVariablenObjekt obTNam seine Referenz (auch, wenn es das letzte aufgerufene ist!). Anderenfalls wird obTNam automatisch auf Nothing gesetzt. Davon kann dann der weitere PgmAblauf abhängig gemacht wdn, wobei im Nothing-Fall noch überprüft wdn muss, ob der Zyklus das 1.Mal (für das Blatt → Return) oder bereits das 2.Mal (für die Mappe) durchlaufen wurde. Dieser Teil könnte auch etwas anders geschrieben wdn:

                If obTNam Is Nothing Then
If Me.Name = relParent.Name Then Return
Else: Call Ausfuehren
End If

Der Rest des Pgms dürfte dir ja (nun) klar sein, wobei das UP beim 2.ZyklusDurchlauf nicht als UP, sondern normal in der BefehlsReihenfolge abläuft. Man kann hier auch noch auf die DoppelAbfrage obTNam Is Nothing verzichten, wenn man die BefehlsReihenfolge* etwas geschickter gestaltet, aber das sollte kaum Geschwindigkeitsvorteile bringen, wäre uU nur eleganter.
Übrigens, das relevante Blatt sollte nicht genauso heißen wie die Mappe, was man aber wohl wg der DateiTypEndung ausschließen kann.
* Dabei könnte auf GoSub verzichtet und stattdessen mit GoTo gearbeitet wdn:

            Set relParent = Me
ob:         For Each obTNam In relParent.Names
If obTNam.Name Like relNam Then
If Not Intersect(Target, obTNam.RefersToRange) Is Nothing Then Exit For
End If
Next obTNam
If obTNam Is Nothing Then
If Me.Name = relParent.Name Then Set relParent = Me.Parent: GoTo ob
Else: Call Ausfuehren
End If

GoTo ob wird dabei nur wirksam, wenn der Vgl True ist. Das ist übrigens eine kleine Falle, aber auch ein Vorteil, bei Notation mehrerer Befehle in einer Zeile!
Du siehst, die Erklärung ist doppelt so lang wie das Pgm und braucht oft auch mehr Zeit, weshalb wohl die Meisten hier (mich eingeschlossen) das selten (ausführlich) und idR ungern tun (gilt auch für FmlLösungen)… ;-)
Morrn, Luc :-?

…der Zyklus durchlaufen wdn, Peter,
bei _Change ist aber alles umgekehrt (vgl unten). Es ist zwar meist besser, alle Zellen eines benannten ZellVerbunds in den NamensBezug aufzunehmen, hier aber nicht unbedingt. Machst du das nur mit der 1.Zelle, erscheint der Name zwar auch im Fenster vor der Edit-Zeile im Xl-Kopf, wird aber in VBA nicht als Name der Auswahl erkannt. Das liegt daran, dass nur alle Zellen eines ZellVerbunds ausgewählt wdn können, aber diese Auswahl keinen Namen hat, nur ihre 1.Zelle. Das ist bei Änderungen aber offensichtlich anders (wie nachfolgend erklärt), was auch zum umgekehrten Verhalten bzgl Zyklus-Durchlauf oder nicht führt.
Was du über meine blauen Ergänzungen/Korrekturen schreibst, ist also tatsächlich verifizierbar. Hier gibt's offensichtlich einen gewaltigen Unterschied zwischen Auswahl (_SelectionChange, mit der ich getestet habe) und Änderung (_Change). Merkürdigerweise umfasst Target bei ersterem in beiden Benennungsvarianten (nur 1. bzw alle Zellen) stets alle Zellen, liefert also eine Anzahl >1, im 2.Fall aber nur die 1.Zelle (also Anzahl=1), was wohl daran liegen mag, dass normalerweise nur die 1.Zelle geändert wdn kann, ohne den Verbund aufzulösen (was auch für unorthodoxe VbZellen, also solche mit Werten in mehreren/allen Zellen, gilt).
Tja, da kannst du mal sehen, dass man auch als gestandener XlUser gelegentlich verblüfft wdn kann. Das mag hier vor allem daran liegen, dass MS es in all den Jahren nicht geschafft hat, ein schlüssiges und einheitliches Konzept für VerbundZellen zu entwickeln. Dieses eigentlich mächtige Werkzeug führt in Xl im GgSatz zu KonkurrenzProdukten eher ein Mauerblümchen-Dasein…
Gruß, Luc :-?

Beliebteste Forumthreads (12 Monate)

Change-Event nur bei Zelle mit benanntem Bereich

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread