Combo box ‘invalid property value’ error (Word VBA)

Topic

New Reply

In a Word VBA userform, we have set the combo box (cbo) to require a match as we don’t want the user to select anything other than what is in the list. However, we do want them to be able to type to be able to quickly find the entry in the list. Unfortunately, if they type something that doesn’t match, they get the Microsoft ‘Invalid Property Value’ error. We would like them to get a more meaningful error message, such as ‘Please select an entry from the list.” We have tried trapping the error to no avail, and have tried using the Exit event when exiting the combo box to see if the value matches something in the list. This works, and will bring up the message box we want, but then it won’t set the focus and select the text in the combo box afterwards. The code we are using is:

Sub cbo_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If cbo.MatchFound = False Then
MsgBox “blah blah”
cbo.SetFocus
With cbo
.SelStart = 0
.SelLength = Len(.Value)
End With
End If
End Sub

Reply | Quote

Replies

Instead of using cbo.SetFocus, set Cancel = True.

Reply | Quote

Thank you so much Hans! Your suggestion worked like a charm. Can I trouble you once more? As I use this hunk of code in many userforms, I decided to make it a function, passing the control and userform name. However, I get an error message about the Cancel = True line indicating that it is an undeclares variable. It is declared as part of the Exit event for the control, from which, I am calling the Function which contains the Cancel = True line. Is there anyway to get around this? Or am I doomed to repeat this chunk of code all over? Your knowledge is greatly appreciated!

Reply | Quote

You could create a procedure like this in a standard module (the type you insert by selecting Insert | Module):

Public Sub HandleExit(cbo As MSForms.ComboBox, Cancel As MSForms.ReturnBoolean)
With cbo
If .MatchFound = False Then
MsgBox “Please select an item from the list.”, vbExclamation
Cancel = True
.SelStart = 0
.SelLength = Len(.Value)
End If
End With
End Sub

This procedure is passed arguments: the combo box and the Cancel argument from the On Exit event procedure. Note that Cancel is not passed as ByVal, so it is passed as ByRef by default. This means that if you change Cancel in this procedure, the changed value is passed back to the calling procedure.

You must call the above procedure in the On Exit event of each combo box:

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
HandleExit ComboBox1, Cancel
End Sub

Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
HandleExit ComboBox2, Cancel
End Sub

etc.

Reply | Quote

You could create a procedure like this in a standard module (the type you insert by selecting Insert | Module):

Public Sub HandleExit(cbo As MSForms.ComboBox, Cancel As MSForms.ReturnBoolean)
With cbo
If .MatchFound = False Then
MsgBox “Please select an item from the list.”, vbExclamation
Cancel = True
.SelStart = 0
.SelLength = Len(.Value)
End If
End With
End Sub

This procedure is passed arguments: the combo box and the Cancel argument from the On Exit event procedure. Note that Cancel is not passed as ByVal, so it is passed as ByRef by default. This means that if you change Cancel in this procedure, the changed value is passed back to the calling procedure.

You must call the above procedure in the On Exit event of each combo box:

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
HandleExit ComboBox1, Cancel
End Sub

Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
HandleExit ComboBox2, Cancel
End Sub

etc.

Reply | Quote

Hans…..you are gooood! It works perfectly….thank you!

Reply | Quote

Hans…..you are gooood! It works perfectly….thank you!

Reply | Quote

Thank you so much Hans! Your suggestion worked like a charm. Can I trouble you once more? As I use this hunk of code in many userforms, I decided to make it a function, passing the control and userform name. However, I get an error message about the Cancel = True line indicating that it is an undeclares variable. It is declared as part of the Exit event for the control, from which, I am calling the Function which contains the Cancel = True line. Is there anyway to get around this? Or am I doomed to repeat this chunk of code all over? Your knowledge is greatly appreciated!

Reply | Quote

Instead of using cbo.SetFocus, set Cancel = True.

Reply | Quote