ExcelBanter - Better error message when a user enters a value in a form comboboxthat is not valid

On 31 Aug, 09:57, Chrisso wrote:
Hi All

I have a form with a combo box that lists valid choices and has "Match
Required" = true.

If a user ignores the pull down on the combo box and enters invalid
data all they get when done is a message saying "Invalid property
value". I cannot see a way of overriding this message like you can do
with cell validation. This message of course meaans nothing to a
normal user and they get very confused.

What is the best way to handle this?

Chrisso?

The solution I have implemented runs as follows:

' stop users typing text into the reason combo box as some user think
they can enter what they like in here
' and then get confused when Excel says "Invalid Property Value"
Private Sub cbxReason_Change()
' jump out if user clears the combo box
If IsNull(Me.cbxReason) Then Exit Sub
If Me.cbxReason = vbNullString Then Exit Sub
' exit if a value is entered via the pull down and appears in the
the combo box row source - a range name of allowable values:
If Value_In_Range_Name_List("REASON_CATEGORIES",
Me.cbxReason.Value) Then Exit Sub

' stop users entering garbage in this combo box before they get a
useless Excel message:
Me.cbxReason = Null
MsgBox "Please use the pull down.", vbExclamation, "Hint"
End Sub

Public Function Value_In_Range_Name_List(sListName As String, sValue
As String) As Boolean
Value_In_Range_Name_List = Not
ThisWorkbook.Names(sListName).RefersToRange.Cells. Find(sValue, ,
xlValues, xlWhole) Is Nothing
End Function

Hope this helps others or spurs someone to show me a better solution.

Chrisso