Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, Br. Columbia
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo box 'invalid property value' error (Word VBA)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo box 'invalid property value' error (Word VBA)

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

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, Br. Columbia
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box 'invalid property value' error (Word VBA)

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo box 'invalid property value' error (Word VBA)

    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.

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, Br. Columbia
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box 'invalid property value' error (Word VBA)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •