Hallo,

ich habe folgenden Code zum Thema, der laut eines Forums super funktionieren sollte, leider liefert die For Each Schleife einen Fehler ("Typen unverträglich"). stimmt da was nicht?

Hier der Code:

Function myList(sh As Worksheet, lngCol As Long)
   Dim vntList(), n As Long, vntC, vntTmp
   Dim myCol As New Collection
   With sh
   ReDim vntList(1 To 1, 1 To Application.CountA(.Columns(lngCol)))
   vntTmp = .Range(.Cells(1, lngCol), .Cells(Rows.Count, lngCol).End(xlUp))
   End With
   For Each vntC In vntTmp
      Err.Clear
      On Error Resume Next
      myCol.Add vntC, CStr(vntC)
      If Err.Number = 0 Then
         n = n + 1
         vntList(1, n) = vntC
      End If
   Next
   ReDim Preserve vntList(1 To 1, 1 To n)
   myList = WorksheetFunction.Transpose(vntList)
End Function

Private Sub UserForm_Initialize()

  ComboBox1.List = myList(Sheets(1), 1)

End Sub