Option
Explicit
Public
Sub
Test_EnumValidationList()
Dim
rngDropDown
As
Excel.Range
Dim
vntItems
As
Variant
Dim
vntItem
As
Variant
Set
rngDropDown = Worksheets(
"Tabelle1"
).Range(
"A1"
)
On
Error
Resume
Next
If
rngDropDown.Validation.Type <> XlDVType.xlValidateList
Then
Call
MsgBox(
"Keine Liste!"
, vbExclamation)
Exit
Sub
End
If
Set
vntItems = rngDropDown.Worksheet.Evaluate(rngDropDown.Validation.Formula1)
On
Error
GoTo
0
If
IsEmpty(vntItems)
Then
vntItems = Split(rngDropDown.Validation.Formula1, Application.International(XlApplicationInternational.xlListSeparator))
End
If
For
Each
vntItem
In
vntItems
Debug.Print vntItem
Next
End
Sub