View original (French)

[VBA Excel] Check validity of ComboBox value

Solved
zabouall Posted messages 6 Status Member -  
lermite222 Posted messages 9042 Status Contributor -
Hello,
I have set up a combobox with a list of values. I chose the property fmStyleDropDownCombo, which allows manual entry of values.

I want to ensure that the entered value is valid so as not to generate an error. I would like to display a warning.

I believe I need to use the 'Change' event, but I'm not sure how!
Thank you for your help!!
Configuration: Windows XP Internet Explorer 6.0

1 answer

  1.
    lermite222 Posted messages 9042 Status Contributor 1 199
     
    To test the combo box text is not as simple as you say.
    Paste this code into the combo box sheet
    Dim EditLigne As Boolean 

    Private Sub ComboBox1_Change() If ComboBox1.ListIndex = -1 Then '-1 when the line is edited EditLigne = True End If End Sub

    Then two solutions are possible or both together
    First solution, the user must press enter to confirm their modification. 
    Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Dim Txt As String 'To test the pressing of the Enter key. If KeyCode = 13 And EditLigne Then 'check the combo text. Txt = ComboBox1.Text Stop 'if good validate '... ComboBox1.AddItem Txt EditLigne = False End If End Sub

    Second solution, the test is done when the user selects something other than the combo. 
    Private Sub ComboBox1_LostFocus() 'To avoid testing the pressing of the Enter key. 'Test when leaving the combo If EditLigne Then 'check the combo text. Txt = ComboBox1.Text Stop 'if good validate '... ComboBox1.AddItem Txt EditLigne = False End If End Sub

    Here are two possible solutions.
    See you later
    --
    Experience teaches more surely than advice. (André Gide)
    2