User form data validation

[jtodd]

Morning all

As part of a user form I am developing the user has to enter a seal number , but it has to be on the seal list .
The list has 3000 numbers ,
How can I make sure the user only picks a number off the list ?

 

[My Aswer Is This]

What is: "seal list"

Do you mean this is a Listbox?

Well if that is all you have on the UserForm for him to select from he must select some value from the listbox. He will not be able to enter anything.

Please give more details.

 

[Norie]

You could use a combobox to list all the 'seal' numbers.

 

[My Aswer Is This]

I would use a Listbox.

Using a Combobox allows the user to choose a value from the combobox or manually enter a value.
A listbox requires the user to only choose a value in the listbox.

 

[jtodd]

Sorry if I was not clear.
The seal list is just a list of 3000 numbers and the user can only enter a number from this list in the user form.
I need some kind of validation to only allow this - limit to list ?
If I use a list box the list will be huge?
Can I use a combo box and the user type in the value and how would I link the list to the combo box ?

Hope this makes things a bit clearer

 

[My Aswer Is This]

So you want to put a number into a combobox and if that number in not one of the 3,000 numbers you want the user to get a message telling him that is not a valid number. Is that what you want?

What is the name of the Combobox?
Where is this list of 3,000 numbers?

 

[Norie]

With a combobox if the user enters a value that's not on the list then the ListIndex property of the combobox will be -1, something that is easily to check.

Private Sub Combobox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim idx As Long
    
    idx = ComboBox1.ListIndex

   If idx = -1 Then
       MsgBox "Invalid seal number selected/entered"
       Cancel = True
   End If

End Sub

 

[My Aswer Is This]

I did not know that. Thanks for that info.

With a combobox if the user enters a value that's not on the list then the ListIndex property of the combobox will be -1, something that is easily to check.

Private Sub Combobox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim idx As Long
    
    idx = ComboBox1.ListIndex

   If idx = -1 Then
       MsgBox "Invalid seal number selected/entered"
       Cancel = True
   End If

End Sub

 

[My Aswer Is This]

The error message you entered in your script pops up any time I manually enter any value in the combobox. And that value is in the Combobox list.

With a combobox if the user enters a value that's not on the list then the ListIndex property of the combobox will be -1, something that is easily to check.

Private Sub Combobox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim idx As Long
    
    idx = ComboBox1.ListIndex

   If idx = -1 Then
       MsgBox "Invalid seal number selected/entered"
       Cancel = True
   End If

End Sub

 

[Norie]

It shouldn't, how did you populate the combobox?

Are you entering the value(s) manually or by selection via the dropdown?