Gruppe
Ereignis
Problem
Die Gültigkeitsliste in Zelle A1 soll die Daten aus Spalte A aus Tabelle2 ohne die Leerzeilen enthalten und sich anpassen.
ClassModule: Tabelle1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim intRow As Integer, intLastRow As Integer
Dim txt As String
If Target.Address <> "$A$1" Then Exit Sub
With Worksheets("Tabelle2")
intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For intRow = 1 To intLastRow
If Not IsEmpty(.Cells(intRow, 1)) Then
txt = txt & .Cells(intRow, 1) & ","
End If
Next intRow
End With
txt = Left(txt, Len(txt) - 1)
With Range("A1").Validation
.Delete
.Add _
Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=txt
End With
End Sub