Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 08:56:18
Textbox in Tabellenblatt ohne Bestätigung in Zelle eintragen
20.04.2024 07:57:54
Tablle fürs E-Auto - Preis wenn kürzer als 60 Min Ladezeit
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1860to1864
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
1860to1864
1860to1864
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Worksheet_Change

Worksheet_Change
07.12.2021 08:45:08
Ulf
Hallo zusammen,
ich bräuchte wieder einmal Eure geschätzte Hilfe.
Ich habe eine Arbeitsmappe mit 2 Blättern. Auf der ersten Seite sind Namen von Mitarbeitern eingetragen. Spalte B Name, Spalte C Vorname. Auf der zweiten Seite habe ich ein Formular, dass via Datenüberprüfung in Zelle C9 auf die Namen zugreift und in Zelle M9 auf die Vornamen.
Nun habe ich mir überlegt, dass es schön wäre, wenn die Zelle M9 nach Möglichkeit automatisch ausgefüllt werden könnte bzw. die möglichen Vornamen von der Auswahl her bei gleichem Namen eingegrenzt würden. Ich hatte da an das Worksheet_Change gedacht, weiß aber nicht wie ich das Ganze angehen soll. Habt Ihr vielleicht Vorschläge für mich?

Sinngemäß
Private Sub Worksheet_Change(ByVal Target As Range)
Wenn C9 auf Seite 2 geändert wird
Dann suche in Spalte B auf Seite 1 nach dem Namen
Und gib den/die Werte aus Spalte C von Seite 1 in M9 auf Seite 2 zur Auswahl aus
End Sub
Gruß Ulf

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Worksheet_Change
07.12.2021 09:43:05
Rudi
Hallo,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim objVN As Object, vntArr, i As Long
If Target.Address = "$C$9" Then
Set objVN = CreateObject("scripting.dictionary")
With Range("M9")
.ClearContents
.Validation.Delete
End With
With Tabelle1
vntArr = .Range(.Cells(2, 2), .Cells(Rows.Count, 3).End(xlUp))
End With
For i = 1 To UBound(vntArr)
If vntArr(i, 1) = Target Then objVN(vntArr(i, 2)) = 0
Next i
If objVN.Count Then
If objVN.Count = 1 Then
Range("M9") = objVN.keys
Else
With Range("M9").Validation
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=Join(objVN.keys, ",")
.IgnoreBlank = True
.InCellDropdown = True
End With
Range("M9").Select
End If
End If
End If
End Sub
Gruß
Rudi
Anzeige
AW: Worksheet_Change
07.12.2021 11:35:17
Ulf
Hallo Rudi,
vielen Dank für Deine Hilfe. Ich versuche gerade Deinen Code zu verstehen. Ich habe Deinen Code in das passende Tabellenblatt gepackt und ausprobiert. Doch leider blieb er gleich bei 

With Range("M9")
.ClearContents
.Validation.Delete
End With
hängen, sodass ich diesen Teil erst einmal auskommentiert habe. Danach klappt es mit Namen die einzeln vorkommen auf Anhieb. Habe ich aber einen Namen der mehrfach vorkommt, dann bricht er wieder ab bei 


.Add _

Type:=xlValidateList, _

AlertStyle:=xlValidAlertStop, _

Formula1:=Join(objVN.keys, ",")
Liegt das eventuell an dem Namen des Blattes Tabelle1(User Übersicht), Tabelle2(Formular)?
Gruß Ulf
Anzeige
AW: Worksheet_Change
07.12.2021 11:48:23
Rudi
Hallo,
lad mal die Mappe hoch
Gruß
Rudi
AW: Worksheet_Change
07.12.2021 14:39:44
Rudi
Hallo,
da verbundene Zellen vorhanden sind, verursacht Range("M9").Clearcontents einen Fehler.
So klappt das:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim objVN As Object, vntArr, i As Long
If Target.Address = "$C$9" Then
On Error GoTo ERREXIT
Application.EnableEvents = False
Set objVN = CreateObject("scripting.dictionary")
With Range("M9").MergeArea
.ClearContents
.Validation.Delete
End With
With Tabelle1
vntArr = .Range(.Cells(2, 2), .Cells(Rows.Count, 3).End(xlUp))
End With
For i = 1 To UBound(vntArr)
If vntArr(i, 1) = Target Then objVN(vntArr(i, 2)) = 0
Next i
If objVN.Count Then
If objVN.Count = 1 Then
Range("M9") = objVN.keys
Else
With Range("M9").Validation
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Formula1:=Join(objVN.keys, ",")
.IgnoreBlank = True
.InCellDropdown = True
End With
Range("M9").Select
End If
End If
End If
ERREXIT:
Application.EnableEvents = True
End Sub
Wenn es zu einem Nachnamen nur einen Vornamen gibt, wird der in M9 eingetragen. Ansonsten (Baltik) wird eine Auswahlliste erstellt und die Zelle ausgewählt.
Gruß
Rudi
Anzeige
AW: Worksheet_Change
07.12.2021 14:57:34
Ulf
Rudi, Du bist der Beste! Vielen Dank für die Hilfe.
Ich habe nicht gewusst, dass verbundene Zellen so einen Ärger machen.
Gruß Ulf
AW: Worksheet_Change
07.12.2021 11:52:25
guenni
Hab Dir mal meine Version hochgeladen. denke die ist verständlicher, aber vermutlich langsamer
https://www.herber.de/bbs/user/149635.xlsm
Zu Rudis Version: Der erste Hänger passiert weil aus dem Change-Ereignis der Zell-inhalt gelöscht wird. entweder das Clearcontents weglassen oder vorher application.enableevents = false
beim zweiten Hänger fehlen vermutlich im Join die Anführungszeichen pro Vorname
vielleicht geht
Formula1:= """" & Join(objVN.keys, """,""") & """"
Anzeige
guenni: Bei mir funktioniert alles. owT
07.12.2021 12:00:58
Rudi
AW: guenni: Bei mir funktioniert alles. owT
07.12.2021 14:10:37
Ulf
So, also ich habe jetzt noch mal probiert und leider bekomme ich die Lösung selber nicht hin.
Das ClearContents habe ich auskommentiert und damit klappen alle Namen die nur 1x vorkommen perfekt.
Wenn allerdings ein Name mehrmals vorkommt verstehe ich das so, dass der Code mir keine Auswahlliste erstellen kann aus denen ich wählen könnte und das Programm ja nun nicht wissen kann welchen Vornamen es wählen sollte oder täusche ich mich da nur? Für mich sind VBA Arrays leider immer noch ein Buch mit 7 Siegeln.
Eine Beispiel Datei hatte ich im vorherigen Post mit beigefügt.
Gruß Ulf
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige