Excel: Paarungen aus Dropdownliste bestimmen

Dieser Thread ist Teil einer Diskussion zu einem Artikel:  Zum News-Artikel gehen

Fritz50

Stammgast
Hallo zusammen

Der Titel sagt ja was das Ergebnis sein soll. Es sind aber ein paar Bedingungen einzuhalten.

Wenn der Inhalt der Dropdownliste in Spalte A ist, kann der Partner/Gegner/Gegenspieler in einer andern Spalte ausgewählt und in die Zelle eingetragen werden (in jeder Zelle ist die ganze Dropdownliste verfügbar. Idealerweise ist die zweite Spalte gleich lang, wobei jede Paarung 2x erscheint z.B. C:H und invers H.C).

Ich möchte nun, dass jeder Name nur einmal gewählt werden kann, oder dass erkannt wird, dass er ein zweites mal gewählt worden ist und in der Folge nicht eingetragen wird (Duplikate suchen, ev. Fehlermeldung). Gleichzeitig könnte auch die inverse Paarung eingetragen werden, d.h. muss sogar damit beide Namen blockiert werden. Und der eigene Name soll logischerweise auch ausgeschlossen werden.

Ich suchen einen Ansatz wie ich das im Worksheet_Change Ereignis behandeln kann.

Vorerst möchte ich das (noch) nicht in einem Formular löschen.

Noch eleganter wäre wohl, wenn in der Dropdownliste nur noch die verbleibenden Namen gewählt werden könnten. Die Dropdownliste resp. die Liste in Spalte A darf nicht verändert werden.
Danke

Gruss
Fritz
 

nochEinAndreas

Stammgast
Hallo Fritz,

hier mal ein paar Ideen:
  • Die Dropdown-Listen in den Zellen sollten nicht aus den Original-Listen (Spalten A und B(?)) gefüllt werden, sondern aus Kopien davon in den Spalten (z.B. C und D). Diese könnten ausgeblendet sein. Oder du benutzt Spalte in ausgeblendeten Blättern.
  • Alternativ kannst du "Kopien" der Spalten in Datenfeldern verwalten.
  • Sobald eine Zelle über das Dropdown-Menü ausgewählt wird, löschst du per Makro die Zelle (in der Spalten-Kopie) oder den Eintrag (im Datenfeld). Damit stellst du sicher, dass dieser Eintrag kein zweites Mal ausgewählt werden kann.
  • Die Gegenpaarung kannst du entsprechend per Makro in die Folgezellen eintragen (vorher EnableEvents auf False setzen und hinterher wieder auf True). Dann noch die entsprechenden Einträge in den Spalten-Kopien oder den Datenfeldern löschen.
  • Nach jeder Änderung musst du die Dropdown-Menüs neu füllen, um die gelöschten Einträge heraus zu nehmen.
So viel mal im "Blindflug". Wenn du mal deine Mappe zeigst, kann ich konkreter werden.

Grüße, Andreas
 

Fritz50

Stammgast
Hallo Andreas,

Ich versuche mal selber deine Denkanstösse umzusetzen. Die Hinweise haben mir schon sehr geholfen. Ich werde eher mit Listen in einem separaten Tabellenblatt arbeiten, weil ich in mehreren "Durchgängen" Paarungen machen möchte. Scheint mir einfacher als Datenfelder (Arrays).

Über ein Problem habe ich mir noch keine Gedanken gemacht und es ist vielleicht nicht ganz einfach. Wenn ich eine (falsche) Paarung wieder löschen muss oder will, muss ich die beiden Namen und Startnummern wieder in die Dropdownliste einbauen, damit diese wieder zu Verfügung stehen. Wie sichere ich den Zellinhalt bevor ich ihn lösche? Ich muss ja wissen welche Werte in die Dropdownliste zurück müssen.
Wenn ich soweit bin oder doch anstehe, werde ich Mappe hier hereinstellen.

Schönen Abend an alle.

Gruss
Fritz
 

nochEinAndreas

Stammgast
Hallo Fritz,

das mit dem Rückgängig machen ist etwas, was immer wieder auftaucht. Da bleibt nichts anderes übrig, als den "Status davor" zu speichern. D.h., du musst außer der ersten Kopie der Original-Listen immer auch eine zweite speichern. Wenn du dann in der ersten Kopie etwas gelöscht hast und das rückgängig machen willst, dann musst du diese erste Kopie durch die zweite ersetzen.
Je nachdem wie viele Rückgängig-Aktionen du erlauben willst, musst du halt eine ganze Reihe von Kopien anlegen. Mit jedem Rückgängig müssen die Kopien wieder um eine Stufe zurück verschoben werden.
So weit ganz grob das Verfahren. Wie du das im Einzelnen ausgestaltest, überlasse ich deiner Phantasie ...

je nachdem, was rückgängig gemacht werden soll, kann das natürlich ganz schön Speicher fressen bzw. die Mappe vergrößern. In Excel z.B. sind standardmäßig nur 16 Undos möglich. Nur mit einem Eingriff in die Registrierung lässt sich das auf max. 100 erhöhen. Ich glaube, die Programmierer wussten, warum sie das begrenzt haben.

Gruß, Andreas
 

Fritz50

Stammgast
Hallo Andreas

Ich bin dann mal zufrieden mit einem Undo, d.h. eine Korrektur nur bei der letzten Eingabe. Allenfalls müsste es möglich sein, die gelöschten ( und "verlorenen" ) Namen manuell einzugeben (weil nicht mehr im Dropdown Menü).
Dann habe ich mal eine Mappe mit neutralen Namen und mehreren Spalten für Einträge mit Dropdown-Auswahl gemacht. Dazu auch ein Blatt "Listen" mit den Werten, die für die Auswahl zur Verfügung stehen sollen; in den gleichen Spalten wie die Dropdown Wahl.
Dann habe ich auch gelesen/gesehen, dass man einzelne Bereiche als Tabellen mit/ohne Überschrift und Namengebung definieren kann, die dann auch noch dynamisch sein sollen. Wenn ich dann für die Eingabe den DropDown-Auswahlbereich anwähle, erhalte ich in den ersten beiden Fällen den Tabellennamen als Auswahlbereich (wenn genau dieser Bereich gewählt ist). In den beiden andern Fällen wird die Tabelle nicht erkannt. Ich habe dazu mehrere Versuch gemacht (die Datenprüfung überschrieben oder auch erst gelöscht und neu eingegeben, oder die Tabellen mit dem Namenmanager gelöscht und neu erstellt).
Wenn irgendwo noch = INDIREKT(... auftaucht, habe das in eine Video gesehen.

Bei der Einstellung der Datenüberprüfung mit einer Liste gibt es die Option "Leere Zeilen ignorieren". Wenn ich aber Einträge in der Liste lösche, sind dort im der Dropdown-Auswahl Lücken.

Gruss
Fritz
 

Anhänge

  • PaarungenDropDown.zip
    16,4 KB · Aufrufe: 5

nochEinAndreas

Stammgast
Hallo Fritz,

ich habe noch nicht in deine Mappe geschaut. Nach deiner Beschreibung klingt es aber, als wäre da ziemlich viel drin.
Leider bin ich jetzt eine Woche lang nicht erreichbar. Ich hoffe, dass sich bis dahin jemand gefunden hat, um diese Dinge zu bearbeiten. Wenn nicht, mache ich mich hinterher daran.

Gruß, Andreas
 

Fritz50

Stammgast
Hallo zusammen
Ich möchte noch eine etwas andere(s) Aufgabe/Thema zur Diskussion stellen: den richtigen Bereich finden.
Ausgangspunkt sei ein Worksheet_Change Ereignis. In welchem Bereich ist die aktive Zelle?
Ist es schlau das mit mehreren If then .. exit Sub zu lösen, oder mit einer Kaskade von Else If ?

If Not Intersect(ActiveCell, rngBereich1) Is Nothing Then ' 2x negiert ist wahr
Code
Exit Sub
End If

If Not Intersect(ActiveCell, rngBereich2) Is Nothing Then ' 2x negiert ist wahr
Code
Exit Sub
End If

If Not Intersect(ActiveCell, rngBereich3) Is Nothing Then ' 2x negiert ist wahr
Code
Exit Sub
End If

Usw. z.B. 10, 12 mal

Ohne Exit Sub könnte durch Veränderung in einer durchlaufen Codesequenz eine Bedingung für eine folgende Bedingung entstehen. Damit wäre eine gemeinsame Codesequenz am Ende der Prozedur ausgeschlossen


Oder mit Else If Kaskade
If Not Intersect(ActiveCell, rngBereich1) Is Nothing Then ' 2x negiert ist wahr
Code

Else If Not Intersect(ActiveCell, rngBereich2) Is Nothing Then ' 2x negiert ist wahr
Code

Else If Not Intersect(ActiveCell, rngBereich3) Is Nothing Then ' 2x negiert ist wahr
Code
usw. mehrere mal noch

Else
Ev. Code

End If



Bezüglich der Auswahl für einen Dropdownbereich ist mir aufgefallen, dass ich bei der Datenüberprüfung als Quelle (mit Liste) den Namen des Bereichs selber eintragen muss (er wird nicht gefunden). Z.B. tabNameG1 = $M$4:$M$10

Gruss
Fritz
 

nochEinAndreas

Stammgast
Hallo Fritz,

erst mal zu deinem letzten Posting #7:
Ich würde das mit Select Case machen:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case Is = "$A$1"
            ' Dein Code für A1
        Case Is = "$A$2"
            ' Dein Code für A2
        Case Is = "$A$3"
            ' Dein Code Für A3
    End Select
End Sub

Da braucht du nix zu schachteln und nix zu kaskadieren.

Manchmal braucht es auch gar keine Tests. Da reicht dann eine Berechnung: Angenommen, du willst abhängig davon, ob etwas in A1, A2 oder A3 geändert wurde den Wert in Blatt "Sheet1", "Sheet2" oder "Sheet3" ändern. Dann könnte man das so lösen:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("Sheet" & Target.Row).Range("A1") = Target
End Sub

Gruß, Andreas
 

Fritz50

Stammgast
Hallo Andreas,

Wenn ich die DropDown-Auswahl mit einem Namen ansprechen will, muss ich den Namen bei der Definition des Auswahlbereichs explizit angeben.
(meine letzte Frage/Feststellung)-

Dann zum Thema Selektion: Im ersten Schritt will ich nicht nach einer einzelnen Zelladresse selektieren, sondern herausfinden in welchem Range die Zelle liegt. Wenn ich Case Is = rng_RangeX schreibe, ist die Adresse der ersten Zelle oben links gemeint.

Dann noch zum Select Case Konstrukt. Ist das nicht einfach eine andere Schreibweise zu If Then ... Else if Then .. Else? Zumindest ähnlich. Es wird ja auch in einer Reihenfolge nach der erfüllten Bedingung gesucht.

Gruss
Fritz
 

nochEinAndreas

Stammgast
Hallo Fritz,

Wenn ich die DropDown-Auswahl mit einem Namen ansprechen will, muss ich den Namen bei der Definition des Auswahlbereichs explizit angeben.
(meine letzte Frage/Feststellung)-
Ist das jetzt ein Frage oder Feststellung? Ich verstehe das nicht so ganz. Wenn du die Dropdown-Auswahl mit einem Namen ansprechen willst, musst du natürlich den Namen angeben. oder wie meinst du das?

Dann zum Thema Selektion: Im ersten Schritt will ich nicht nach einer einzelnen Zelladresse selektieren, sondern herausfinden in welchem Range die Zelle liegt. Wenn ich Case Is = rng_RangeX schreibe, ist die Adresse der ersten Zelle oben links gemeint.
Es geht ja wohl um deine Datei "PaarungenDropDown", oder? Da würde ich mit Select Case nach der Spalte der Zelle fragen:
Code:
Select Case Target.Column
   Case 4
   ...
   Case 5
   ...
   Case 7
   ...
   Case 8
   ...
End Select

Dann noch zum Select Case Konstrukt. Ist das nicht einfach eine andere Schreibweise zu If Then ... Else if Then .. Else? Zumindest ähnlich. Es wird ja auch in einer Reihenfolge nach der erfüllten Bedingung gesucht.
Jain. "If Then ... Else" ist immer die Unterscheidung zwischen einem Fall und allen anderen Möglichkeiten. "If Then" (ohne Else) ist die Frage ob ein bestimmter Fall vorliegt. Bei "Select Case" kannst du viele verschiedene Fälle behandeln. Du könntest also gleichsetzen
Code:
Select Case a
   Case 1
   ...
   Case 2
   ...
   Case 3
   ...
End Select
mit
Code:
If a = 1 Then
   ...
End If
If a = 2 Then
   ...
End If
If a = 3 Then
   ...
End If

Ansonsten: Ich bin eben an einer Alternativ-Version zu deiner "PaarungenDropDown". Gib mir noch 2-3 Tage Zeit.

Grüße, Andreas
 

nochEinAndreas

Stammgast
Hallo Fritz,

die Alternativ-Version zu deiner Mappe ist fertig. Ich gebe zu, es war mehr logistische Arbeit als ich erwartet hatte. Dabei habe ich das automatische Eintragen der "Gegenpaarungen", das du gerne wolltest, noch gar nicht drin.
Ich muss gestehen, dass ich nicht verstanden habe, was du mit den beiden Tabellen auf Blatt "Listen" in deiner Mappe bezweckt hast. Ich habe das in meiner Version mal weggelassen.
Die einzelnen Codeteile in meiner Mappe habe ich hoffentlich ausreichend dokumentiert, damit sie verständlich sind. Hier hilft auch immer gut, die Einzelschritte mit F8 durchzugehen.
Kurz zur Arbeitsweise meiner Mappe:
Nach dem Starten der Mappe ist sie sozusagen "jungfräulich": In Spalte A und B stehen die Mannschaften (?), die ausgewählt werden können. Diese Liste bleibt immer unverändert.
In den grün umrandeten Bereichen kannst du per Dropdown die Mannschaften auswählen, entweder über die Startnummer, dann wird automatisch der Name ergänzt, oder über den Namen, dann wird die Startnummer ergänzt.
Jede Mannschaft, die ausgewählt wurde, wird für die weitere Auswahl aus den Dropdownlisten entfernt.
Dann gibt es noch den Zurück-Knopf, der aktiviert wird, sobald eine Auswahl getroffen wurde. Mit ihm kannst du die letzte Auswahl rückgängig machen. Dann ist er bis zur nächsten Auswahl wieder deaktiviert.
Das Blatt "Listen" ist das "Steuerblatt". Hier steht in den Spalten A und B immer die "eingedampfte" Liste, die für die Dropdowns gebraucht wird. Am Anfang ist sie gleich der Komplettliste.
Für die Rückgängig- und Lösch-Aktionen werden 2 Kopien der Auswahlbereiche von Blatt "Startliste" gebraucht: In den Spalten D,E und G,H steht die Kopie, wie sie vor dem Change-Ereignis aussah. In den Spalten J,K und M,N, so wie sie nach dem Change aussieht.

Es wird hoffentlich deutlich, wie alles zusammenhängt, wenn du etwas mit der Mappe rumspielst.
Und vielleicht hilft es der Ausarbeitung deiner eigenen Mappe weiter.

Gruß, Andreas
 

Anhänge

  • PaarungenDropDown-Vorschlag Andreas.zip
    37,6 KB · Aufrufe: 4

Fritz50

Stammgast
Hallo Andreas,

Für die Einträge Paarung und Gegenpaarung habe ich m.E. eine ganz gute Lösung gefunden. Sie ist in der Mappe "Ranges" realisiert.
Darin ist auch eine Abfrage IsEmpty(Range). Ich wollte damit die Prozedur verlassen, wenn ich Teile der Tabelle lösche. Das scheint aber nicht zu funktionieren. Wenn die erste Zelle des gelöschten Range in einer der benutzten Spalten liegt, wird am Ende der Liste ein Wert eingetragen, weil IsEmpty nicht geht und anschliessend keine Übereinstimmung gefunden wird.
Dann geschieht es oft, dass, wenn ich Codezeilen ändere, nachher die Ereignisse nicht mehr aufgerufen werden. Das auch wenn ich die Schaltfläche zurücksetzten drücke. Ich muss dann Excel ganz schliessen und wieder öffnen, damit die Applikation wieder läuft. Ich glaube nicht, dass ich irgendwo das Application.EnableEvents nicht zurücksetzte.

Gruss
Fritz
 

Anhänge

  • PaarungenDropDown3.zip
    33,4 KB · Aufrufe: 3

nochEinAndreas

Stammgast
Hallo Fritz,

Darin ist auch eine Abfrage IsEmpty(Range). Ich wollte damit die Prozedur verlassen, wenn ich Teile der Tabelle lösche. Das scheint aber nicht zu funktionieren. Wenn die erste Zelle des gelöschten Range in einer der benutzten Spalten liegt, wird am Ende der Liste ein Wert eingetragen, weil IsEmpty nicht geht und anschliessend keine Übereinstimmung gefunden wird.
Ja. Hast du dir meinen Code aus Posting #11 man angeschaut? Da stand ich nämlich genau vor dem gleichen Problem: Wenn das Change-Ereignis ausgelöst wird, weil der Zellinhalt gelöscht wurde, weiß der Makro nicht mehr, was vorher drin stand. Deswegen habe ich zu der Lösung mit 2 Kopien der Auswahl gegriffen. Eine Kopie wird beim Öffnen der Arbeitsmappe erstellt. Das ist der Status vor dem Change-Ereignis. In der Change-Prozedur wird auf diese Kopie zugegriffen, um den Vorher-Zustand zu ermitteln. Am Ende des Change-Ereignisses wird der neue Zustand wieder auf die Kopie geschrieben, quasi wieder als Vorher-Zustand des nächsten Change-Ereignisses.
ich hatte ja in meinem letzten Posting geschrieben, dass die Logistik recht kompliziert ist. Bitte schau dir meinen Code mal an und versuche ihn zu verstehen. Wenn du Fragen hast melde dich gerne.

Dann geschieht es oft, dass, wenn ich Codezeilen ändere, nachher die Ereignisse nicht mehr aufgerufen werden. Das auch wenn ich die Schaltfläche zurücksetzten drücke. Ich muss dann Excel ganz schliessen und wieder öffnen, damit die Applikation wieder läuft. Ich glaube nicht, dass ich irgendwo das Application.EnableEvents nicht zurücksetzte.
Von welcher Schaltfläche schreibst du? Ich habe in deiner Mappe keine gefunden. Oder meinst du den Zurücksetzen-Button im VBA-Editor? Dann wäre das verständlich: Angenommen, du hast ein Application.EnableEvents = False im Code. Wenn du diese Zeile dann im Einzelschrittmodus (F8) ausführst, dann steht er auf False. Daran ändest du auch nichts, wenn du den Zurücksetzen-Button klickst. Da das eine Application-Eigenschaft ist, wird sie Excel-übergreifend gesetzt. Den Makro zu beenden, ändert daran nichts. Du musst dann im Direktbereich wieder Application.EnableEvents = True setzen. Excel zu machen und neu Starten hat den selben Effekt. Da wird Application.EnableEvents auch auf True gesetzt.

Gruß, Andreas
 

Fritz50

Stammgast
Hallo Andreas,

Danke für deine Unterstützung. Deine Ideen haben mich weitergebracht. Ein gutes Sparring!
Als Zurücksetzen-Button habe ich schon das Viereck (Karo) im VBA-Editor gemeint. Ich habe dann auch den Direktbereich gefunden, aber im konkreten Fall noch nicht eingesetzt.
Dann zum Zurücksetzten/Löschen: Wahrscheinlich kann man nicht alle Optionen vorsehen. Der Fall, bei dem mehrere Zellen gelöscht würden (markieren, delete), der dann mit IsEmpty abgefangen werden sollte, "beisst" sich wahrscheinlich mit dem Fall "letzte Paarung zurücknehmen".
Mit "grossflächigem" Delete kann man die DropDowmlisten auch kaum mehr wiederherstellen.
Mir schwebt noch ein anderer Lösungsansatz vor: Weil die Paarungen nicht mehr in der DropDown-Liste sind und umgekehrt, werde ich versuchen die Daten zwischen diesen beiden Listen hin und herzuschieben (Jedes Werepaar ist am einen oder andern Ort, kommt also immer nur einmal vor).
Vielleicht werde ich die Paarungen noch nummerieren.

Gruss
Fritz
 

nochEinAndreas

Stammgast
Hallo Fritz,

schön, dass du weiter kommst.
ich denke, wenn du ganze Bereiche löschen willst müsste das gehen, indem du im Change-Ereignis das Target "zerlegst".
Wenn du auf einmal einen ganzen Bereich löschst, ist ja Target der komplette Bereich.
Du müsstest also mit For Each arbeiten:
For Each zelle in Target
' hier dann den Code für eine einzelne Zelle
Next zelle.

Gehen tut das bestimmt. Der Aufwand ist natürlich ein Bisschen größer, aber wenn du es für eine einzelne Zelle sauber hinbekommst, geht es auch für einen ganzen (zerlegten) Bereich.

Gruß, Andreas
 

Fritz50

Stammgast
Hallo Andreas
Im Moment habe ich noch die eine oder andere Baustelle. Ich denke, wenn ich einen grösseren Bereich lösche, muss/will/kann ich die DropDownliste nur als vollständige Liste wieder übernehmen, aber nicht rückwärts "zurückbauen", ist aber okay für mich. Um im Ereignis die Löschaktion sauber zu erkennen, muss ich jede Zelle im Bereich prüfen (If Target.Cells.Count = Application.WorksheetFunction.CountBlank(Target) Then).
Um den letzten gelöschte Zellinhalt zu merken, habe ich eine Variable deklariert, die ich bei jedem Worksheet_SelectionChange beschreibe und im Fall einer DeleteAktion darauf zurückgreifen kann. Über den Inhalt (Startnummer) finde ich dann die betroffene Paarung und die entsprechenden DropdownWerte schon.
Was mich aber eingeholt hat ist die Fehlermeldung "Laufzeitfehler 28", nicht genügend Stapelspeicher.
Dazu muss ich sagen, dass ich immer wieder eine Tabelle kopiere und diese dann weiter entwickle/ändere, incl. VB-Code. Der Fehler kommt bereits beim Hochfahren oder öffnen des Files, aber (fast sicher) nur bei der 32-Bit Version. Auf dem andern Rechner mit der 64-Bit läuft die Applikation.
Und wenn der Fehler auftritt, schliesst das Programm gleich wieder oder bleibt auch stehen, so dass ich einen Abbruch mit dem Taskmanager machen muss. In diesem Fall kann ich eigentlich gar nie mehr auf die Applikation zurückgreifen, z.B. Code ändern oder Tabellen löschen.
Wenn ich dazu debuggen wählen kann, ist der Fehler in dieser Zeile:

If Not Intersect(Target, Range("D5 : AF14")) Is Nothing Then

Vielleicht magst ein paar Gedanken schreiben. Danke

Gruss
Fritz
 

nochEinAndreas

Stammgast
Hallo Fritz,

nur mal kurz zum Fehler 28: Es gibt hier Hinweise von MS:

Kommt denn der Fehler, bevor überhaupt ein Makro läuft? Dann kann es kaum etwas mit VBA zu tun haben. Oder kommt er bei einem Makro-Lauf (ganz früh, z.B. in einem Workbook_Open). Dann kannst du mal am Anfang dieser Prozedur ein Stop-Kommando setzen. Dann speichern, schließen und neu öffnen. Excel springt bei der Stop-Anweisung in den VBA-Editor und du kannst mit F8 weiter machen, bis der Fehler auftritt.

So viel nur kurz dazu.

Guß, Andreas
 

Fritz50

Stammgast
Hallo Andreas
Noch eine Rückmeldung zum Thema Laufzeitfehler 28 und nicht genügend Stapelspeicher.
Ich habe in einer Ereignisprozedur die EnableEvents Eigenschaft nicht abgefragt und gesetzt. Wenn dann in dem Ereignis eine Zelle oder ein Bereich verändert wird, führt das wieder zu einem Ereignis, das nicht geblockt wird. So entsteht/entstand die Endlosschlaufe. Hab lange gebraucht, bis ich das realisiert habe. Warum die 64-Bit Version von Excel, das verkraftet hat, kann ich nicht erklären. Ich habe Code im Change- und im SelectionChange-Ereignis.
Danke für deine Hilfe. Im Moment ist das okay für mich.
Schönen Abend.

Gruss
Fritz
 
Oben