CODEWITHSUNDEEP
excelseparatorvalidationvba
user3588043
user3588043

Reputation: 136

Excel datavalidation list separator

I have datavalidation on list

I need First entry will be "Enter Value" and separate this entry from other list items by inserting blank row or line, as it will be like selectable header entry. Separator must be not selectable when walking trought the list

Something like that

Enter value

Item1
Item2
Item2

or

Enter value
------------
Item1
Item2
Item3

Without using COMBOBOXes please!!!

Upvotes: 0

Views: 1599

Answers (1)

mattboy
mattboy

Reputation: 2910

The best you can probably do is add those two new values to the list, then catch when invalid values are selected with the Worksheet_Change event. For example, when A1 has the validation list:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1") = "Enter value" Or Range("A1") = "______" Then
    MsgBox ("OMG! Even though the value is selectable, you may not select it. Select something else please.")
    Range("A1") = ""
End If

End Sub

Upvotes: 1

Related Questions