Normalerweise wird über die Funktion Daten / Datentools / Datenüberprüfung die Gültigkeit für eine Zelle oder einen Zellbereich individuell festgelegt. In diesem Beispiel zeigen wir Ihnen, wie sich die Gültigkeitsprüfung anwendergesteuert und dynamisch durch einen Optionsbutton verändern lässt, siehe Abbildung 1.
Abbildung 1: Gültigkeitsprüfung per Optionsbutton festlegen
Ein Klick auf den Optionsbutton "Liste A" führt dazu, dass in der Gültigkeitsprüfung die Daten aus Liste A angezeigt werden. Ein Klick auf den Optionsbutton B hingegen zeigt die Einträge aus Liste B an.
Die Daten der Liste A befinden sich auf dem Tabellenblatt "Liste_A" und die Daten der Liste B auf dem Sheet "Liste_B".
Den beiden Listen wurden dazu jeweils den Bereichsnamen "Liste_A" und "Liste_B" übergeben, siehe Abbildung 2.
Abbildung 2: Namensmanager mit der Namensvergabe für Liste A und B
Wie Sie sehen, wird über die Funktion BEREICH.VERSCHIEBEN() der Bereichsname dynamisch aufgebaut, so dass die Liste jederzeit ergänzt werden kann und die Einträge entsprechend an den Bereichsnamen übergeben werden.
Grundsätzlich stehen zwei Varianen zur Verfügung. Die erste Variante verwendet VBA zur dynamischen Anzeige der ausgewählten Liste. In der zweiten Variante wird komplett auf VBA verzichtet.
Variante 1: Verwendung von VBA
Nachdem die Namen eingerichtet sind können wir uns den VBA-Code etwas näher ansehen.
Durch einen Klick auf den jeweiligen Optionsbutton wird ein VBA-Makro ausgeführt, welches die Datenüberprüfungsfunktion so anpasst, dass jeweils der richtige Bereichsname übergeben wird.
Die VBA-Makros für die Optionsschaltflächen lauten wie folgt:
Optionsschaltfläche 1 (Liste A)
Private Sub OptionButton1_Click() '** Optionsschaltfläche Liste A With Range("A5:A20").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Liste_A" End With End Sub
Optionsschaltfläche (Liste B)
Private Sub OptionButton2_Click() '** Optionsschaltfläche Liste B With Range("A5:A20").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Liste_B" End With End Sub
Nach Aktivierung der Option "Liste B", wird der Gültigkeitsprüfung im Bereich B5 bis B20 die Datenliste "Liste B" übergeben und die Einträge aus Liste B stehen entsprechend zur Verfügung, siehe Abbildung 3.
Abbildung 3: Datengültigkeitsliste von Liste B
Variante 2: Verwendung von Formeln - Ohne VBA
In der zweiten Variante wird eine Lösung vorgestellt, die komplett auf VBA verzichtet. Das Anlegen der Listen A und B funktioniert genauso wie bei der VBA-Variante. Nur das Zuweisen der entsprechenden Liste wird über eine Formel gesteuert.
Gehen Sie dazu wie folgt vor:
- Verknüpfen Sie die beiden Comboboxen (Formularsteuerelement) mit der Zelle C6
- Damit wird bei Auswahl der Combobox Liste A eine 1 in C6 eingetragen und bei Auswahl von Liste B eine 2. Wenn Sie die ActiveX Optionsbutton verwenden, wird stattdessen WAHR und FALSCH in die Zellen geschrieben. Diese müssen Sie über eine geeignete Wenn-Formel nur noch in die Ziffern 1 bzw. 2 übersetzen. Z. B. so: =wenn(C6="WAHR;1;2)
- Markieren Sie nun im nächsten Schritt den Zellbereich, für den Sie die Gültigkeitsprüfung einrichten möchten. Im Beispiel also den Bereich A5:A20
- Starten Sie die Gültigkeitsprüfung über das Menü Daten / Datentools / Gültigkeitsprüfung
- und Erfassen Sie als Quelle folgende Formel: =WAHL($C$6;Liste_A;Liste_B), siehe Abbildung 4.
Abbildung 4: Erfassung der Formel für die dynamische Gültigkeitsprüfung - Wenn Sie nun die Datenprüfung mit einem Klick auf die Schaltfläche OK abschließen funktioniert dar Wechsel zwischen Liste A und B ebenfalls dynamisch über einen Klick auf die entsprechende Optionsschaltfläche.
- Die leicht modifizierte Lösung für ActiveX-Optionsschaltfläche finden Sie ebenfalls in der Beispieldatei.
Eine grundlegende Einführung zum Thema "Gültigkeitsprüfung" finden Sie unter dem folgenden Link: http://www.excel-inside.de/funktionen-a-tipps/177-verwendung-der-gueltigkeitspruefung
Die Beispieldatei in der beide Varianten noch mal gezeigt werden, können Sie über den folgenden Link herunterladen und für Ihre Zwecke modifizieren.