How can I use an Enum for Validation in VBA

It's not as straightforward as you would wish, but there is a way to do this.

The underlying value of an Enum is of type Long. So it's a number, not a string. Only the VBA compiler knows about AA; this gets translated into 0 everywhere else (and BB is 1, PP is 2, etc.). So the first thing to do is to explicitly associate a string to each enum option. I've done this using functions like this, which are easy to make but do require a little bit of manual maintenance each time you add, remove, or modify an enum option:

Function AccountEnumString(a As account) As String
    Select Case a
        Case AA: AccountEnumString = "AA"
        Case BB: AccountEnumString = "BB"
        Case PP: AccountEnumString = "PP"
        Case ZZ: AccountEnumString = "ZZ"
        Case Else: Err.Raise 9999, , "Unexpected enum value."
    End Select
End Function

Then you want to loop through all enum options, making a list of their associated string. You could in principle say

Dim a As account
For a = AA To ZZ
'...

But that will be very difficult to maintain: what if you add another enum option such that ZZ isn't the last anymore? Then you'll have to change your code. A better option is to put a little more work in the enum declaration itself:

Public Enum account
    [_First] = 1
    AA = 1
    BB = 2
    PP = 3
    ZZ = 4
    [_Last] = 4
End Enum

Here you explicitly assign values to each option (rather than letting the compiler assign defaults) and you also add a First and a Last indicating the bounds of the enum. These require manual maintenance, but this is trivial in comparison to the trouble of maintaining the rest of the code as would be required without this trick.

Note on syntax: the _ underscore makes the _First and _Last elements hidden (they will not be displayed in the VB Editor's autocompletion) and the [ square brackets ] make the underscore character valid for use as the first character of a variable name. (source of this trick)

Now you can loop through your enum options, collect the associated strings in an array, join it with , delimiters, and use that as validation formula:

Dim a As account
Dim arrValidationList() As String

ReDim arrValidationList(account.[_First] To account.[_Last])
For a = account.[_First] To account.[_Last]
    arrValidationList(a) = AccountEnumString(a)
Next

With Range("C9").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(arrValidationList, ",")
End With

Note that each time you add or remove enum options, the following things will require manual maintenance:

• The account enum definition, making sure that the numbering is sequential and that the First and Last elements actually represent the bounds, and
• The AccountEnumString function, making sure the strings are representative of the enum options to your satisfaction

Also see these somewhat related questions:

• Use a builtin Enum in a cell formula with a UDF
• How can I use enum identifiers in Excel UDF