error when entering number in combobox

[Allround_it_er]

I have a column A, containing 4 digits numbers all starting with 1, 2, 3, 4 or 5.

The starting number represents departments. So there are 5 departments.

Every department contains different items. So the numbers go for example from 1001 to 1067, from 2001 to 2134, and so on. The item numbers are stored in column A, the items in column B and the name of the department in column C.

I have a combobox on a userform. This userform is filled with all the Numbers from column A with this code:

cboZoekProduct.list = Range("tabel3515").Value

The user can pick a number from the combobox, but he can also enter a number manually.

After picking a number from the combobox, a listbox is filled with the three columns.

When the user enter a number that doesn't exists, i get an error.

When the user enters a number starting with a "1" that is higher than the highest number in the column starting with a "1", I get an error.

This is my code that isn't working:




Private Sub cboZoekProduct_AfterUpdate() ' Is het getal groter dan het hoogste cijfer beginnend met 1, 2, 3, 4 of 5 Dim rij1 As Long Dim rij2 As Long Dim rij3 As Long Dim rij4 As Long Dim found1 As Range Dim found2 As Range Dim found3 As Range Dim found4 As Range Dim CellValue1 As String Dim CellValue2 As String Dim CellValue3 As String Dim CellValue4 As String Set found1 = Sheets("Producten_nieuw").Columns("A").Find(what:="2***", LookIn:=xlValues, lookat:=xlWhole) rij1 = found1.Row - 1 CellValue1 = Range("A" & rij1).Value If cboZoekProduct > CellValue1 Then MsgBox "Het ingegeven artikelnummer is groter dan " & CellValue1 End If Set found2 = Sheets("Producten_nieuw").Columns("A").Find(what:="3***", LookIn:=xlValues, lookat:=xlWhole) rij2 = found2.Row - 1 CellValue2 = Range("A" & rij2).Value If cboZoekProduct > CellValue2 Then MsgBox "Het ingegeven artikelnummer is groter dan " & CellValue2 End If Set found3 = Sheets("Producten_nieuw").Columns("A").Find(what:="4***", LookIn:=xlValues, lookat:=xlWhole) rij = found3.Row - 1 CellValue3 = Range("A" & rij3).Value If cboZoekProduct > CellValue3 Then MsgBox "Het ingegeven artikelnummer is groter dan " & CellValue3 End If Set found4 = Sheets("Producten_nieuw").Columns("A").Find(what:="5***", LookIn:=xlValues, lookat:=xlWhole) rij = found4.Row - 1 CellValue4 = Range("A" & rij4).Value If cboZoekProduct > CellValue4 Then MsgBox "Het ingegeven artikelnummer is groter dan " & CellValue4 End If 'If cboZoekProduct.Value > CellValue1 Then ' MsgBox "Het getal dat je ingegeven hebt werd niet gevonden!", vbCritical, "Niet gevonden!" ' Else 'Set found = Sheets("Producten_nieuw").Columns("A").Find(what:="2***", LookIn:=xlValues, lookat:=xlWhole) ' If found Is Nothing Then ' MsgBox "Not found" 'Else ' MsgBox "Found on row " & found.Row ' End If 'rij = found.Row - 1 'MsgBox (rij) 'CellValue11 = Range("A" & rij).Value 'MsgBox "Het laatste artikelnummer beginnend met 1 = " & CellValue 'If cboZoekProduct.Value > CellValue1 Then ' MsgBox "Het getal dat je ingegeven hebt werd niet gevonden!", vbCritical, "Niet gevonden!" ' ' End If Dim LastPosition As Long Const PatternFilter As String = "*[!0-9]*" Const MaxLen As Long = 4 ' If you do not want to restrict the number of characters, ' simply set the MaxLen to a huge number like (2^31-1) Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With cboZoekProduct If .Text Like PatternFilter Or Len(.Text) > MaxLen Then Beep MsgBox "Je kan niet meer dan 4 cijfers ingeven!", vbCritical, "Niet meer dan 4 cijfers!" SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End With End If SecondTime = False 'Place any other TextBox1_Change event code here '************************************************************************************************ If cboZoekProduct.Value <> "" Then ' txtArtikelnummer.Value = [tabel3515].Find(cboZoekProduct, , xlValues).Offset(0, 0) ' txtArtikelbenaming.Value = [tabel3515].Find(cboZoekProduct, , xlValues).Offset(0, 1) ' txtAfdeling.Value = [tabel3515].Find(cboZoekProduct, , xlValues).Offset(0, 2) ' TextBox1.Value = [tabel3515].Find(cboZoekProduct, , xlValues).Offset(0, 0) ' lblAfdeling.Visible = True End If If txtAfdeling.Value = "Brood" Then optBrood.Value = True lblAfdeling.Caption = txtAfdeling.Value ElseIf txtAfdeling.Value = "Brood diepvries" Then optBroodDiepvries.Value = True lblAfdeling.Caption = txtAfdeling.Value ElseIf txtAfdeling.Value = "Boterkoeken" Then optBoterkoeken.Value = True lblAfdeling.Caption = txtAfdeling.Value ElseIf txtAfdeling.Value = "Patisserie" Then optPatisserie.Value = True lblAfdeling.Caption = txtAfdeling.Value ElseIf txtAfdeling.Value = "Traiteur" Then optTraiteur.Value = True lblAfdeling.Caption = txtAfdeling.Value End If End Sub

What is going wrong? Any help would be much appreciated!





Also the code is put in the afterupdate event, I would like it better in the change event, but I get an error after entering the first digit in the combobox.



Thx in advance