Office: Zellen verknüpfen in versch. Tabellenblättern- ohne Zirkelbe

Hallo!

Ich habe folgendes eigentlich simples, aber für Excel wie immer zu kompliziertes, Problem:

In Tabellenblatt 1 wird in der Zelle A1 der Wert 5 angezeigt.
In Tabellenblatt 2 soll in der Zelle B2 der selbe Wert wie in A1 angezeigt werden, also in dem Fall 5.
A1 und B2 sollen also den selben Wert anzeigen, jedoch sollen beide Zellen veränderbar sein und sich automatisch anpassen.
A1=B2 und B2=A1 ---> ein Zirkelbezug also!

Ich habe mich bereits durch Foren geschlagen und weiss, dass das nur mit einem VBA-Code zu machen ist. Jedoch konnte ich keinen finden, der sich auch auf Zellen aus verschiedenen Tabellenblättern bezieht. Wäre echt super, wenn mir jemand sagen könnte, wie das geht!! :)

Mit VBA kenn ich mich übrigens kaum aus...also würde mich noch mehr freuen, wenn mir jemand sagen könnte, wie man die Makros richtig abspeichert, weil bis jetzt liefen diese Vorgänge eher chaotisch ab :D z.B. ist das eine Makro nur beim erstmaligen Tabellenöffnen aktiv und danach nicht mehr...

Bin über jede hilfreiche Antwort glücklich!!! :) Vielen Dank!

 

Hallo kathy123,

ich habe in der angehängten Beispielmappe mal einen Code nach Deinen Vorgaben eingebaut.
Er bezieht sich auf Tabelle1 A1 und Tabelle2 B2.

Eine Zellwertänderung löst das "Change-Ereignis" aus.
Allerdings, wenn man bei einer Änderung in A1 die Zelle B2 auf dem 2. Blatt per Code ändern lässt, löst auch diese Änderung das entsprechende "Change-Ereignis" aus, und es entsteht auch in VBA ein Zirkelbezug.

Um solch eine Endlos-Schleife zu vermeiden, habe ich in einem allgemeinen Modul erst einmal eine öffentliche variable definiert, die in allen Modulen dieser Arbeitsmappe abgefragt bzw. benutzt werden kann.

Code:

Option Explicit

Public bolChange As Boolean

Als nächstes habe ich in den Modulen der Blätter 1 und 2 den folgenden Code eingefügt:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me
        If Target.Address = "$A$1" Then
            If bolChange = False Then
                bolChange = True
                ThisWorkbook.Worksheets("Tabelle2").Cells(2, 2).Value = Target.Value
            Else
                Exit Sub
            End If
        End If
    End With
    bolChange = False
End Sub

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me
        If Target.Address = "$B$2" Then
            If bolChange = False Then
                bolChange = True
                ThisWorkbook.Worksheets("Tabelle1").Cells(1, 1).Value = Target.Value
            Else
                Exit Sub
            End If
        End If
    End With
    bolChange = False
End Sub

Bei mir funktioniert soweit alles.
Wenn noch etwas an Deine Anforderungen angepasst werden muss, etwas nicht funktioniert, oder jemand eine bessere Idee hat, bitte melden.

Ich hoffe geholfen zu haben.

 

Hallo!

Man könnte auch temporär die Ereignisse abschalten:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" Then Sheets("Tabelle2").Range("B2") = Target
Application.EnableEvents = True
End Sub

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$B$2" Then Sheets("Tabelle1").Range("A1") = Target
Application.EnableEvents = True
End Sub

 

Juhuuu, es funktioniert!!! Vielen lieben Dank fette Elfe!!! :)

@schatzi: was bedeutet "temporär abschalten"? die Funktion sollte ja immer vorhanden sein, nicht temporär? :/

 

Und jetzt hätte ich doch noch eine Frage:

Wie macht man's am geschicktesten, wenn man ganze Spalten auf dieselbe Art verknüpfen möchte? Also ich will nicht jede einzelne Zelle eingeben müssen, sondern einen ganzen Bereich z.B.: A1:A10 (Tabelle1) mit B1:B10 (Tabelle2)


Vielen Dank im voraus! :)

 

Hallo kathy123,

damit meint schatzi:
das Ereigniss "Worksheet_Change" zeitweilig ausschalten, eben damit kein Zirkelbezug entsteht.
Denn eine Zellwertänderung auf Blatt 1 löst dieses Ereignis aus, damit wird der Code in Gang gesetzt und übernimmt diese Änderung auf Blatt 2.
Dieses Übernehmen der Änderung bewirkt aber eine Zellwertänderung auf Blatt 2, und dies löst dann ebenfalls wieder dieses Ereignis aus, wodurch der Code gestartet wird um die Änderung auf Blatt 1 zu übertragen.
Dies löst wiederum... usw.
Dadurch würde eine Endlosschleife entstehen die theoretisch niemals aufhört.
Deshalb ist eine Möglichkeit, dieses Ereignis temporär auszuschalten.
Aber nie vergessen es danach sofort wieder einzuschalten.
;O)

Keine Ahnung, aber ich hätte einen Vorschlag.
;O)

Ich habe es jetzt so gelöst, dass Du im Modul von Tabelle1 und Tabelle2 die entsprechenden Bereiche in gewohnter Schreibweise eingeben kannst.
("A1:A10"...)
Die Übernahme der Zellwerte auf das jeweils andere Blatt ist um eine Spalte verschoben:
Tabelle1 Spalte A = Tabelle2 Spalte B usw. und umgekehrt.

D.h. Du musst schon bei der Eingabe im Code diese Regel beachten.
Bsp:
Modul Tabelle1:

Code:

Set rgBereich = Union(.Range("A1:A10"), .Range("B1:B10"), .Range("C1:C10"))

Modul Tabelle2:

Code:

Set rgBereich = Union(.Range("B1:B10"), .Range("C1:C10"), .Range("D1:D10"))

Die Schreibweise hast Du ja jetzt vorgegeben.
Du kannst soviele Bereiche mit "Union" verbinden wie Du möchtest.

Das bedeutet aber auch, die Spalte A in Tabelle2 kann nicht auf Tabelle1 gespiegelt werden, denn das wäre dort ja dann die Spalte links neben A, und die gibt es nicht.
Gleiches gilt für die letzte Spalte (256) von Tabelle 1.

Falls diese generelle Regel (verschieben um eine Spalte) nicht in Deinem Sinne sein sollte, dann müsstest Du mal genauere Angaben machen.

Code:

Option Explicit

Public bolChange As Boolean

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgZelle As Range
Dim rgBereich As Range

    With Me
        Set rgBereich = Union(.Range("A1:A10"), .Range("B1:B10"), .Range("C1:C10"))
        If Not Intersect(Target, rgBereich) Is Nothing Then
            If bolChange = False Then
                bolChange = True
                For Each rgZelle In rgBereich
                    If rgZelle.Column < ThisWorkbook.Worksheets("Tabelle2").Columns.Count Then
                        ThisWorkbook.Worksheets("Tabelle2").Cells(rgZelle.Row, rgZelle.Column + 1).Value = rgZelle.Value
                    End If
                Next rgZelle
            Else
                Exit Sub
            End If
        End If
    End With
    bolChange = False
End Sub

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgZelle As Range
Dim rgBereich As Range

    With Me
        Set rgBereich = Union(.Range("B1:B10"), .Range("C1:C10"), .Range("D1:D10"))
        If Not Intersect(Target, rgBereich) Is Nothing Then
            If bolChange = False Then
                bolChange = True
                For Each rgZelle In rgBereich
                    If rgZelle.Column > 1 Then
                        ThisWorkbook.Worksheets("Tabelle1").Cells(rgZelle.Row, rgZelle.Column - 1).Value = rgZelle.Value
                    End If
                Next rgZelle
            Else
                Exit Sub
            End If
        End If
    End With
    bolChange = False
End Sub

 

Achim, danke dir nochmal! :) wir kommen der Sache schon näher...
Aber das Verschieben um eine Spalte ist in meiner vorgegebenen Tabelle doch nicht möglich. Ich muss flexibel die Datensätze in Tabelle1 A1:A10 mit Tabelle2 X5:X15 verknüpfen, da sie sich nicht mehr neu sortieren lassen.
Kann doch nicht sein, dass sowas bei Excel so schwer zu machen ist? Wieso benutzt es dann jeder XD

Warte weiterhin hoffnungsvoll auf Antworten! :))) THX

 

Besser als nix ;O)

Hallo kathy 123,
möglicherweise kann es sein, dass genau Dein Wunsch sich schwer umsetzen lässt. Dafür lassen sich tausende andere aber leicht umsetzen.
Aaaaaber, solange hier niemand weiß was genau Du suchst, wie Deine Spezifikationen aussehen, solange kann auch niemand entscheiden wie schwierig die Lösung ist.

Also, was willst Du genau?
Eine unbestimmte Anzahl von Bereichen, in unbestimmter Größe, mit einem entsprechenden Bereich auf einem anderen Blatt "verknüpfen"?
Und dabei sollen die Bereiche auf beiden Blättern frei wählbar sein, es gibt also keine Regeln "verschiebe Bereich um x und y"?.
Ist das jetzt so richtig?

Sind die Bereiche denn bleibend, sprich wenn Du sie einmal festgelegt hast, verändern sie sich irgendwann in Anzahl, Größe, Ort?
Oder müssen sie öfters neu angepasst werden?
Welche Möglichkeit besteht bei Deinen Anforderungen diese Bereiche überhaupt zu definieren?
Könnte man ein Blatt (versteckt) in Deine Datei einfügen, wo die zu verknüpfenden Bereiche in Spalten gegenüber gestellt werden, sodass ein Makro dort auslesen kann, welche Bereiche zueinander gehören?
Könnte man anhand von Spaltenüberschriften oder sonstigen Zellbezügen die Bereiche automatisch definieren oder gegenseitig zuordnen lassen?

Ohne ganz genaue Beschreibung (am besten sogar noch mit Beispieldatei), ist es in meinen Augen nicht sinnvoll weiter nach einer Lösung zu suchen.
Denn nach jedem Vorschlag änderst Du die Vorgaben.

 

:D ok, danke. hier nochmal die ganzen angaben möglichst präzise:

Ich habe Tabellenblatt1, in dessen Zellen A2:A10 stehen folgende Werte: A, B, C, D...H

Tabellenblatt1

A B C

1 -
2 -
3 A
4 B
5 C
6 D
7 E
8 F
9 G
10 H


Und Tabellenblatt 2, in dessen Zellen F5:F12 genau die selben Werte wie in Spalte A2:A10 in Tabellenblatt 1 stehen soll.

Tabellenblatt 2

A B C D E F

1
2
3
4
5 A
6 B
7 C
8 D
9 E
10 F
11 G
12 H

Und nun noch folgende Bedingungen:
1) Es soll egal sein, ob ich die Werte A-H in Tabellenblatt 1 oder 2 ändere, sie sollen stets die selben sein. Das heisst, wenn ich z.B "D" bei "A6"in Tab1 zu "Y" umändere, soll auch "Y" bei F8 in Tab2 stehen. Dies soll automatisch geschehen und ist somit ein Zirkelbezug.
2) Ferner sollten mit dem Code gleich mehrere Spalten auf einmal verknüpfbar sein. Das heisst, dass ich die 3 Spalten "A2:A10 , D5:D20, S3:S40" von Tab1 mit den Spalten "F5:F12, H5:H20, R13:R50" in einem Code verknüpfen möchte.
Ausserdem sieht man an dieser Bedingung, dass ich selbst frei wählen möchte, wohin ich die Spalte "A" verknüpfe. "A2:A10" sollte auf "F5:F12" verknüpfbar sein, ebenso wie auf zB "J3:J11" oder "K102:K110".
3) Die Reihenfolge der mir gegebenen Daten lässt sich nicht mehr ändern, ebenso würde ich nur ungern ein extra Tabellenblatt für diese Funktion einfügen. Das einzige was sich ändern wird, sind die Werte in den Spalten (also in meinem Beispiel werden sich nur A-H ändern bzw sieht es in echt sogar so aus, dass die Zellen momentan leer sind und später gefüllt werden sollen)
4) Spaltenüberschriften sind auch schon vergeben, es lässt sich also wirklich nichts mehr an den Daten ändern.
5) Die Größe der Bereiche ist auch jedes Mal anders (A2:A10 oder auch K103:K110). Worauf ich mich beschränken kann ist, dass es immer Spalten und keine Matritzen sind (noch nicht).

 

Ferner habe ich schon einen Lösungsansatz gefunden, jedoch will er noch nicht so ganz in meinem konkreten Fall funktionieren:

Hier die allgemeine Lösung (die einwandfrei funktioniert):

'Codemodul von Tabelle 1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Sheets("Tabelle2").Range("D5:D14").Value = Sheets("Tabelle1").Range("A1:A10").Value
End If
End Sub

'Codemodul von Tabelle 2
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D5:D14")) Is Nothing Then
Sheets("Tabelle1").Range("A1:A10").Value = Sheets("Tabelle2").Range("D5:D14").Value
End If
End Sub


Und die an meine Bedingungen leicht abgeänderte Lösung:

Tabelle1 Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B3:B39 , C3:C39, D3:D39, E3:E39")) Is Nothing Then
Sheets("Kiste").Range("C9:C44").Value = Sheets("Kosten").Range("B3:B39").Value
Sheets("Kiste").Range("F9:F44").Value = Sheets("Kosten").Range("C3:C39").Value
Sheets("Kiste").Range("J9:J44").Value = Sheets("Kosten").Range("D3:D39").Value
Sheets("Kiste").Range("H9:H44").Value = Sheets("Kosten").Range("E3:E39").Value

End If
End Sub



Tabelle 4 Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C9:44, F9:F44, J9:J44, H9:H44")) Is Nothing Then
Sheets("Kosten").Range("B3:B39").Value = Sheets("Kiste").Range("C9:C44").Value
Sheets("Kosten").Range("C3:C39").Value = Sheets("Kiste").Range("F9:F44").Value
Sheets("Kosten").Range("D3:D39").Value = Sheets("Kiste").Range("J9:J44").Value
Sheets("Kosten").Range("E3:E39").Value = Sheets("Kiste").Range("H9:H44").Value

End If
End Sub

Jedoch erscheint hier stehts der Laufzeitfehler 9 in Tabelle 1 bei "If not Intersect...". Soweit ich verstanden habe, müsste das am falschen Namen der Tabllen liegen, wobei ich es sowohl mit "KISTE" und "KOSTEN" versucht habe als auch mit der Nummer der Tabellen, also statt KISTE "Tabelle 1" und statt KOSTEN "Tabelle 2". Nun denke ich, dass der Fehler womöglich wo anders liegt???

Ich bin jetzt SO nah dran an der Lösung!! :) Ganz bisschen noch!!! :)

 

Hallo kathy123,
das dürfte daran liegen, dass in folgende Zeile:

Code:

If Not Intersect(Target, Range("C9:44, F9:F44, J9:J44, H9:H44")) Is Nothing Then

ein "C" fehlt.
Und zwar hier:

Code:

Range("C9:44

Dein Problem mit den Blattnamen liegt folgendermaßen:
Wenn Du in Tabelle 1 ("Kiste") folgende Zeile benutzt:

Code:

Sheets("Kiste").Range("C9:C44").Value = Sheets("Kosten").Range("B3:B39").Value 

dann werden bei jeder Änderung des Focus in Tabelle1 die Werte aus Tabelle2 übernommen.
Du hast im Code für beide Blätter die Blattnamen falsch gesetzt.
Diese Zeile muss so lauten:

Code:

Sheets("Kosten").Range("C9:C44").Value = Sheets("Kiste").Range("B3:B39").Value

Wenn Du mit Deinem Code so zufrieden bist, okay.
Dann freuts mich das Du Deine Lösung hast.
Aber ich halte ihn nicht für schlau.
1. Verzichtest Du auf jedwede Referenzierung. Das kann gutgehen, kann aber zu ungeahnten Problemen führen.
2. Benutzt Du "Sheets". Excel unterscheidet alle Blätter aber in verschiedene Kategorien, und da Du Dich auf Tabellenblätter beziehst, würde ich "Worksheets" benutzen. Bzw. "Me" solange Du Dich auf das Blatt beziehst, in dessen Modul Du gerade den Code schreibst (ist kürzer und übersichtlicher).
3. Arbeitest Du mit dem "SelectionChange"-Ereignis. Solange Du eine einzelne Zelle auswählst, deren Wert veränderst, und danach mit der Enter-, der Tab-, den Cursor-Tasten oder der Maus die Zelle wieder verlässt, sprich eine andere auswählst, funktioniert die Wertübernahme ins andere Blatt. Aber markiere mal einen Bereich von mehreren Zellen und lösche die Werte (Entf-Taste), oder füge Werte mit Copy/Paste ein. In so einem Fall musst Du den Fokus nicht versetzen, und deshalb werden die Werte erstmal auch nicht übernommen. Dass muss kein Problem sein, kann aber. Ich würde es verwirrend finden.
4. Dein Code springt bei jeder Verschiebung des Fokus innerhalb der definierten Bereiche auf einem der beiden Blätter an und übernimmt alle Werte aus allen angegeben Bereichen ins andere Blatt, egal ob Du etwas geändert hast oder nicht. Das muss kein Problem sein, ist aber eigentlich extrem unnötig. Und falls Dein Rechner eh mal "humpelt" oder Du in einem Netzwerk arbeitest, besonders wenn Deine Datenmengen groß sind oder dadurch viele Formeln neu berechnet werden, dann kann das zu nervigen Verzögerungen führen.
5. Ist die Definition der Bereiche im Code auf diese Art und Weise extrem fehleranfällig (wie Du ja schon gesehen hast). Aber da Du immer noch nicht klar geantwortet hast, ob es eine überschaubare Anzahl von Bereichen (so 4-5 Stück), oder auch erheblich mehr werden können, mache ich mir auch nicht die Arbeit eine eventuell besser Lösung zu überlegen. Generell würde ich es wohl in irgendeiner Art mit Variablen machen. Ist zwar mehr Code, aber richtig aufgebaut, viel besser anzupassen wenn sich etwas verändert. Am benutzerfreundlichsten wäre wohl ein extra Tabellenblatt (kann man ja ausblenden) wo die Bereiche jeweils gegenüber gestellt werden, und dann vom Code ausgelesen werden. Aber diese Variante kommt für Dich ja nicht in Frage.

Da nur Du weißt über wieviele Bereiche wir reden, und wie oft diese geändert und angepasst werden, kannst auch nur Du entscheiden ob Deine Lösung so für Dich sinnvoll ist.


Du hast zwar versucht Deine Rahmenbedingungen möglichst genau zu beschreiben, aber nach "3) Die Reihenfolge" und "4) Spaltenüberschriften" hatte ich garnicht gefragt, ich will ja auch garnichts an den Daten ändern.
Andererseits hast Du etwas (möglicherweise) extrem wichtiges nicht beantwortet:
Nämlich ob es irgendwie möglich ist die Bereiche automatisch definieren zu lassen.
Stehen sie immer unter den gleichen Überschriften, immer unter bestimmten Summierungen oder was auch immer.
Sprich kann man anhand des Tabellenaufbaus die Bereiche automatisch suchen, definieren und verknüpfen lassen?
Dann müsstest Du sie nicht immer manuell ändern.
Falls der Tabellenaufbau dies hergibt fände ich DASS am allerschlauesten.
Aber ohne Beispieldatei...

 

Habe das gleiche Problem nur in Microsoft Excel 2007.
Wäre super, wenn mir einer helfen könnte.
Dafür auch ein Tipp von mir, wer fotobücher
gestalten möchte kann das dort machen.
Liebe Grüße

 

Hallo LordesMan,

wenn das Problem das gleiche ist, ist dann nicht auch die Lösung die gleiche?
Ob Excel2003 oder 2007 macht bis hierher keinen Unterschied.

Falls die bisherige Lösung für Dich nicht brauchbar ist, musst Du schon genauere Angaben machen.