Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1868to1872
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1868to1872
1868to1872
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Data Validation 2v. Tabellen (VBA)

Data Validation 2v. Tabellen (VBA)
02.02.2022 11:23:24
Joe
Hallo,
ich habe hier eine Datei mit 2 Tabellenblätter (Kunden und tbl_data).
Ich möchte per VBA programmieren, dass in der Tabelle "Kunden" Strassen in Spalte A und die zugehörigen Hausnummer (in Funktion der Strasse) in Spalte B auswählbar sind.
Wie kann ich da vorgehen?
Ich habe z.B. schon eine data Validation in Tabelle "Kunden" erstellt per VBA.
Wie kann ich eigentlich hier die Tabelle mit Sheet1 ansprechen, wenn z.B. an Tabellenname "Kunden" der Name verändert wird?
Kann ich mit Range auch die Liste "Strassen" per VBA erstellen, habe diese nun manuell erstellt.

Sub ListeErstellenStrassen()
With Sheets("Kunden").Range("A2:A50000").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=Strassen"
End With
End Sub

Datei:
https://www.herber.de/bbs/user/150814.xlsm
Danke und beste Grüsse, Joe

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Data Validation 2v. Tabellen (VBA)
02.02.2022 13:31:31
Yal
Hallo Joe,
Du musst zuerst deine Strassenliste transponieren: Bereich mit Strassenliste markieren, auf (z.B.) O1 gehen, "Einfügen", "Transponieren",
dann das Ergebnis als aktive Tabelle schalten, Strg+T oder "Einfügen", "Tabelle", Strassenamen bilden dann die Überschriften.
Benenne diese Tabelle im "Tabellentools"-Menü in "Strassenliste",
dann füge in A2:Ax von der Tabelle "Kunden" die Datenüberprüfung
=INDIREKT("Strassenliste["&$A2&"]")
Wie das per VBA gemacht werden kann, hast Du schon im Griff: ersetzte "=Strassen" durch
=INDIREKT("Strassenliste["&$A2&"]")
Gilt nur für B2, der Rest musst per Autofill befüllt. Per Makrorekorder kommst Du auf:

range("B2").AutoFill Destination:=Range("B2:B50"), Type:=xlFillDefault
Du brauchst übrigens nicht im voraus diese Validation bis Zeile 50.000 einzurichten, weil Du eine aktive Tabelle verwendest: beim Eintragen von Werte in erste freie Zeile unter der Tabelle, z.B. neue Kundenname, erweitert sich die Tabelle und deren Formatierungen, inkl. Validation, automatisch.
VG
Yal
Anzeige
AW: Data Validation 2v. Tabellen (VBA)
04.02.2022 14:04:47
Joe
Hallo Yal,
vielen Dank für deine Hilfe. :)
Ich habe die Tabelle transponiert, benannt und es auch hinbekommen per Data Validation, die korrekten Hausnummern anzeigen zu lassen.
Wenn ich dies allerdings per VBA programmiere bekomme ich ein Syntax-Error:

With Sheets("Kunden").Range("B2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=INDIRECT("Strassenliste["&$A2&"]")"
End With
Und das folgende hier verstehe ich nicht, was meinst du damit und wozu brauche ich dies ?

Gilt nur für B2, der Rest musst per Autofill befüllt. Per Makrorekorder kommst Du auf:
range("B2").AutoFill Destination:=Range("B2:B50"), Type:=xlFillDefault
Danke und beste Grüsse, Joe
Anzeige
AW: Data Validation 2v. Tabellen (VBA)
04.02.2022 17:55:47
Yal
Hallo Joe,
die Formel INDIREKT baut eine Adresse zusammen. Diese hat feste und variable Anteile. Die feste werden mit Ausführungszeichen abgegrenzt. Wenn diese in eine VBA-String übergeben werden sollen, müssen diese Ausführungszeichen gedoppelt werden, um nicht teil von der VBA-Anweisung sondern von Formel verstanden zu werden:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=INDIRECT(""Strassenliste[""&$A2&""]"")"
Autofill: Wenn Du eine Formel in B2 schreibst, wie z.B. "=A2" und diese nach unten erweiterst, dann ändert sich diese Formel in der nächste Zeile in "=A3".
Normalerweise kann man über VBA mehrere Zellen übereinander ab B2 mit dieser Formel befüllen 

Range ("B2:B10").FormulaLocal = "=A2"
und in jede Zeile kommt dann "=A3", "=A4", "=A5", usw.
Leider ist es bei der Validation nicht so. Wenn Du die Validation auf B2:B10 mit =INDIREKT(... &A2) festlegst, dann sind alle Zellen B2 bis B10 auf A2 gerichtet.
Die Lösung dagegen, ist in VBA diese Validation auf B2 einzugeben und die Erweiterung nach unten zu simulieren.
Aber wie gesagt, wenn Du eine aktive Tabelle verwendest, erweitert sich diese einstellung von selbst.
VG
Yal
Anzeige
AW: Data Validation 2v. Tabellen (VBA)
08.02.2022 16:37:48
Joe
Vielen Dank für die Erklärungen. :)
Was ich noch toll finden würde, wäre wenn im Dropdown der Hausnummern nicht so viele leere Stellen wären (am Ende, wo die Tabelle der Hausnummern leer ist).
Wie könnte ich dies anpassen und auch dass wenn ich die Anfangsbuchstaben des Strassennamens (oder Hausnummer) eintippe, dann automatisch der vollständige Namen vorgschlagen wird?
Beste Grüsse, Joe
AW: Data Validation 2v. Tabellen (VBA)
08.02.2022 16:41:03
Joe
Vielen Dank für die Erklärungen. :)
Wie könnte man einstellen, dass wenn man den beim Strassennamen z.B. nur die ersten 3 Buchstaben eingibt, der vollständige Strassenname vorgeschlagen wird?
Dies ist bestimmt eine andere Funktion wie Data Validation oder?
Beste Grüsse, Joe
Anzeige
AW: Data Validation 2v. Tabellen (VBA)
08.02.2022 17:21:05
Yal
Hallo Joe,
Um nur die befüllte Zellen aufgelistet zu haben, müsstest Du eine aktive Tabelle pro Spalte haben. Dann hätte jede Tabelle ihre eigene Name. Man könnte den Strassenname verweden, sodass diese doppelt wäre

=INDIRECT(""""&$A2&""[""&$A2&""]"")"
Für die vertkürzte Auswahl habe ich keine Ahnung. Da musst Du rumspielen, probieren.
VG
Yal

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige