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]

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

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

If I select them from the list all works well.
But if I enter a value manually which is in the list I get your error message.

The Combobox is loaded like this:


ComboBox1.List = Range("E1:E3000").Value

 

[Norie]

Are there any trailing/leading spaces in the range E1:E3000?

 

[My Aswer Is This]

No. For testing puposes I just entered 1 into row 1 and 2 into row 2 and then dragged down.

Are there any trailing/leading spaces in the range E1:E3000?

 

[Norie]

And you are just entering 1, 2, 3 or whatever?

 

[My Aswer Is This]

Yes

 

[My Aswer Is This]

And to just assure you here is your script I'm using
It's actually Combobox9
And list loads into combox9

Private Sub ComboBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim idx As Long
    
    idx = ComboBox9.ListIndex
   If idx = -1 Then
       MsgBox "Invalid seal number selected/entered"
       Cancel = True
   End If
   
   Cells(1, 8).Value = ComboBox9.Value
End Sub

 

[jtodd]

Manualy , just need to check the number is valid ( on the list )
The list is on the same sheet at the moment but can be stored anywhere

 

[My Aswer Is This]

Your proving very little information here.
I asked in post #3 :

In post 3 I asked this question and you never answered it.
What is the name of the Combobox?
Where is this list of 3,000 numbers?

And in your last post you said:

The list is on the same sheet at the moment but can be stored anywhere

You did not give me the name of the Combobox and when I ask where is this list I need an exact location. You only said on my sheet. There are millions of places on your sheet.

And I guess from what your saying you have not loaded this list into the Combobox.

So if you enter some number into the combobox you want the script to search for that value from a list of values somewhere on your sheet to see if that is a valid number.

Your lack of answering specific questions is not helping and I can not provide a answer till you answer those specific questions with specific details

 

[jtodd]

No ! just a simpl list of numbers no spaces and the list will not change

Thanks for taking time to help

 

[jtodd]

Sorry I am not trying to make it difficult , I appreciate any help.

So start again , As part of my form the user has to enter a "Seal" number , this has to be in a range - 7647001 - 7650000.
And after talking to the end user they do not want a combo box but make the user enter the "seal" number and if not in the range to show an error message.
Aklo not to allow a "seal" number that has been used.
I said the list is on the same sheet as it does not realy matter where it is but I did state that it was E1 to E3000.

Hope this explains things a bit better, again appologies for not being clear.