AW: Listenfeld in Arbeitsmappe
20.10.2021 09:27:01
Nepumuk
Hallo Udo,
in Das Modul "DieseArbeitsmappe":
Option Explicit
Private Sub Workbook_Open()
Call Worksheets("Tabelle1").FillDictionary ' Tabellenname anpassen
End Sub
In das Modul der Tabelle:
Option Explicit
Private mobjDictionary As Object
Private Sub Worksheet_Change(ByVal Target As Range)
Dim objRange As Range, objCell As Range
Dim objListObject As ListObject
Set objRange = Intersect(Target, Columns(7))
If Not objRange Is Nothing Then
For Each objCell In objRange
If Not IsEmpty(objCell.Value) Then
If Not mobjDictionary.Exists(objCell.Value) Then
Call FillDictionary
For Each objListObject In ListObjects
With objListObject.DataBodyRange.Columns(7).Validation
Call .Delete
Call .Add(Type:=xlValidateList, Formula1:=Join(mobjDictionary.Keys, ","))
.ShowError = False
End With
Next
End If
End If
Next
Set objRange = Nothing
End If
End Sub
Public Sub FillDictionary()
Set mobjDictionary = CreateObject(Class:="Scripting.Dictionary")
Dim objListObject As ListObject
Dim avntValues As Variant, vntItem As Variant
For Each objListObject In ListObjects
avntValues = objListObject.DataBodyRange.Columns(7).Value
For Each vntItem In avntValues
If Not IsEmpty(vntItem) Then mobjDictionary.Item(vntItem) = vbNullString
Next
Next
End Sub
Gruß
Nepumuk