On 31 Aug, 09:57, Chrisso <chris75sut...@gmail.com> 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
|
Similar Threads
