Datenüberprüfung (Liste) via VBA

Forumthread: Datenüberprüfung (Liste) via VBA

17.10.2018 08:17:33

emersonlakeandpalmer

Hallo,
bin zwar ganz neu hier, programmiere aber seit ewigen Zeiten in VB6.
Jetzt wurde an mich die komplexe Aufgabe herangetragen die bestehenden Funktionalitäten (Sourcecode in VB6 ~400 DIN A4 Seiten)
ausschließlich in VBA zu realisieren.
Einer der ersten für mich noch nicht nachvollziehbaren Problemstellungen ist das Ausführen der Datenüberprüfung via Liste (aus einem anderen Sheet des aktuellen Workbooks).
Manuell ausgeführt funktioniert das zwar tadellos. Aber selbst wenn ich das automatisch aufgezeichnete Makro:
Columns("J:J").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Keywords!Z1S9:Z51S9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Nochmals ablaufen lasse, meldet Excel: Laufzeitfehler 1004: Anwendungs- oder objektdefinierter Fehler.
Wohlgemerkt der VBA-Code wurde von EXCEL aufgezeichnet.
Kann mir da jemand weiterhelfen.
Grüße
elp

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Das kommt darauf an, ob Du....

17.10.2018 08:52:15

Case

Hallo Emerson, Lake & Palmer (tolle Gruppe), :-)
... in den Optionen die "Z1S1-Bezugsart" eingestellt hast, oder nicht (Buchstaben oder Zahlen im Spaltenkopf). Außerdem spricht VBA Englisch. Manche aufgezeichneten Dinge sind einfach falsch. ;-)
Probiere es mal so: ;-)

Option Explicit
Sub Main()
Dim strTMP As String
If Application.ReferenceStyle = xlR1C1 Then
strTMP = "=Keywords!R1C9:R51C9"
Else
strTMP = "=Keywords!$J$1:$J$23"
End If
With Columns("J:J").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strTMP
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Servus
Case

AW: Das kommt darauf an, ob Du....

17.10.2018 09:27:12

emersonlakeandpalmer

Danke Case!
Hilft jedoch leider nicht weiter.
Der bei mir eingestellte Application.ReferenceStyle ist xlR1C1
Das du ja vorschlägst, als Adressierung R1C9... statt Z1S9... zu verwenden hat keine Auswirkung.
Noch eine Idee?
Gruß
elp
P.S.: ELP war wirklich eine fantastische Band.
Bin in meiner Jugend extra von Berlin nach Freiburg gefahren um mir eines ihrer Konzerte zu gönnen.
Mußte diese aber zeitweise verlassen, weil mir meine Eingeweide von dem infernalischem Schalldruck der
ALTEC-LANSING 'Rutschen'drohten den Dienst zu verweigern.

Einfacher ist es die (Prüf)Liste zu benennen

17.10.2018 09:24:15

EtoPHG

Hallo,
Wie Case schon erwähnt hat, ist die Wahl der Bezugsart wichtig.
In seinem Code müsste übrigens heissen "=Keywords!$I$1:$I$51".
Um diese Abhängigkeit der Option-Bezugsart zu umgehen, ist einfach diesem Bereich einen Namen (über den XL Namensmanager) zu verpassen, z.B. WerteListe
Dann kann der Code so aussehen:

Formula1:="=WerteListe"

Für die Umstellung von VB6 auf XL-VBA ist es wichtig das Objektmodell (Objekte, Methoden, Eigenschaften) von Excel zu verstehen. Dann wirst Du u.a. auch sehen, dass z.B. .Select ein meist kontraproduktiver und unnötiger Befehl ist, der durch die direkte Referenzierung der Objekte (hier z.B. With ActiveSheet.Columns(9)) ersetzt werden kann.
Gruess Hansueli

AW: Einfacher ist es die (Prüf)Liste zu benennen

17.10.2018 09:53:45

emersonlakeandpalmer

Herzlichen Dank an Case und EtoPHG,
Ihr habt mir sehr schnell weitergeholfen.
Gruß
elp

Bitte Nachricht GELÖST in die anderen Foren! (owT)

17.10.2018 10:19:10

EtoPHG

Bei office-fragen.de (aber ohne CP) hat er sich ..

17.10.2018 12:16:22

lupo1

... auch noch angemeldet, also 4 Foren.
Anscheinend ist er ein Forentester.

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Öffne dein Excel-Dokument und wechsle zu dem Arbeitsblatt, in dem Du die Datenüberprüfung einrichten möchtest.
Öffne den VBA-Editor:
- Drücke ALT + F11.
Erstelle ein neues Modul:
- Rechtsklick auf "VBAProject (dein Datei-Name)" > Einfügen > Modul.

Füge den folgenden VBA-Code ein:

Option Explicit
Sub Main()
   Dim strTMP As String
   If Application.ReferenceStyle = xlR1C1 Then
       strTMP = "=Keywords!R1C9:R51C9"
   Else
       strTMP = "=Keywords!$J$1:$J$51"
   End If
   With Columns("J:J").Validation
       .Delete
       .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:=strTMP
       .IgnoreBlank = True
       .InCellDropdown = True
       .InputTitle = ""
       .ErrorTitle = ""
       .InputMessage = ""
       .ErrorMessage = ""
       .ShowInput = True
       .ShowError = True
   End With
End Sub

Schließe den VBA-Editor und führe das Makro aus, um die Datenüberprüfung einzurichten.

Häufige Fehler und Lösungen

Laufzeitfehler 1004: Anwendungs- oder objektdefinierter Fehler:
- Überprüfe, ob der Bereich, den Du in der Formula1-Eigenschaft angibst, korrekt ist.
- Stelle sicher, dass der Arbeitsblattname richtig geschrieben ist und dass das Arbeitsblatt existiert.
Fehler bei der Bezugsart:
- Achte darauf, ob Du die richtige Bezugsart (Z1S1 oder R1C1) in deinen Excel-Optionen eingestellt hast.

Alternative Methoden

Verwendung von benannten Bereichen:
- Du kannst den Bereich, den Du für die Liste verwenden möchtest, einen Namen geben (z.B. WerteListe) und den Code wie folgt anpassen:
```
.Add Type:=xlValidateList, Formula1:="=WerteListe"
```
Direkte Referenzierung:
- Anstelle von .Select kannst Du direkt auf das Objekt zugreifen, um die Effizienz zu erhöhen:
```
With ActiveSheet.Columns(9).Validation
```

Praktische Beispiele

Listen aus einem anderen Arbeitsblatt:
```
.Add Type:=xlValidateList, Formula1:="=Keywords!$A$1:$A$10"
```
Hierbei wird die Liste von Zelle A1 bis A10 im Arbeitsblatt "Keywords" verwendet.
Dynamische Listen:
- Wenn Du eine Liste in einer Tabelle hast, kannst Du die Tabelle als Datenquelle für die Datenüberprüfung verwenden:
```
.Add Type:=xlValidateList, Formula1:="=Tabelle1[Spaltenname]"
```

Tipps für Profis

Fehlerbehebung: Nutze Debug.Print im Code, um Werte zu überprüfen und Fehlerquellen zu identifizieren.
VBA-Dokumentation: Nutze die Excel VBA-Hilfe, um mehr über die verschiedenen Methoden und Eigenschaften zu lernen.
Regelmäßige Backups: Erstelle regelmäßige Backups deiner Makros, um Datenverlust bei Fehlern zu vermeiden.

FAQ: Häufige Fragen

1. Wie kann ich eine Dropdown-Liste in Excel erstellen? Um eine Dropdown-Liste zu erstellen, verwende die Datenüberprüfung mit dem Typ xlValidateList und gib die Quelle für die Liste an.

2. Was ist der Unterschied zwischen R1C1 und A1 Bezugsart? R1C1 verwendet eine relative Adressierung, während A1 die traditionelle Spalten-Buchstaben und Zeilen-Zahlen verwendet. Du kannst die Bezugsart in den Excel-Optionen einstellen.

3. Wie kann ich eine Liste dynamisch aktualisieren? Verwende einen benannten Bereich oder eine Excel-Tabelle, um die Quelle deiner Datenüberprüfung dynamisch anzupassen.

Beliebteste Forumthreads (12 Monate)

Datenüberprüfung (Liste) via VBA

Forumthread: Datenüberprüfung (Liste) via VBA

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Datenüberprüfung via VBA in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Datenüberprüfung (Liste) via VBA

Forumthread: Datenüberprüfung (Liste) via VBA

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

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Datenüberprüfung via VBA in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread