Error when entering a number in combobox that doesn't exist

Allround_IT_er · Aug 5th 2022

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:

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:

Code

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

Display More

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

Justin Doward · Aug 13th 2022

Hi AL,

If you are still interested in a solution for this please upload an example workbook with the code not working. It is possible to recreate your sheet and test it, but it would be easier if you had it ready to go.

Regards

Justin

Allround_IT_er · Aug 13th 2022

Hi JD,

Thanks for wanting to help me! Much appreciated.

I have found some solutions like detecting if the number if higher than the highest number in a row beginning with a certain number (1,2,3,4,5).
I have a new problem now, If a user enters a number higher than the highest number in the row, a MsgBox should appear, saying that the number entered is to high and after clicking "OK" on the MsgBox, the backgroundcolor of the TextBox should be a different color (reddish), the value that was entered (number to high) should be selected, so that the user can re-enter a valid number without first deleting the wrong-entered number.

- Detecting the number works fine

- Changing the backgroundcolor works fine

- selecting the wrong-answered number works fine

It seems not possible to select the TextBox after clicking OK on the MsgBox without hiding the main UserForm, showing the MsgBox, clicking OK and re-show the main UserForm. I have create my own MsgBox, but that didn't help.

When I enter a valid number, the ListBox shows the correct filter, but the backgroundcolor stays modified and I cannot enter the TextBox again by clicking on it.

I would like that when a wrong number is entered, a MsgBox (self-made or not) appears, telling the entered number was wrong, click OK on the MsgBox and the "wrong-entered value in the TextBox is selected, and the user can re-enter a correct number (while value is selected, the value is automatically deleted when user enters a new value).

Any Help would be appreciated. Thx in advance.

Here is the code that doesn' do as I want (It isn't cleaned yet but it gives an idea:

Code

CellValue1 = Sheets("Producten_nieuw").Range("K5")
CellValue2 = Sheets("Producten_nieuw").Range("K6")
CellValue3 = Sheets("Producten_nieuw").Range("K7")
CellValue4 = Sheets("Producten_nieuw").Range("K8")
CellValue5 = Sheets("Producten_nieuw").Range("K9")

'Set LastCell = Sheets("Producten_nieuw").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
'    LastCell.Select
'Set LastCell = LastCell.Offset(0, -2)

'Set LastCell = Range("A" & Rows.Count).End(xlUp) 'second way to determine value of last cell in column "A"
    
If txtZoekOpArtNr.Value = "" Then
Me.lstboxArtGevonden.Clear

Exit Sub
End If
Const PatternFilter As String = "*[!0-9]*"
Const MaxLen As Long = 4
With txtZoekOpArtNr
    If .Text Like PatternFilter Or Len(.Text) <> MaxLen Then
        txtZoekOpArtNr.BackColor = &HC0C0FF
        MsgBox "Het artikelnummer moet 4 cijfers bevatten!", vbCritical, "Niet meer dan 4 cijfers!"
        GoTo focus
    Else
    If txtZoekOpArtNr > CellValue1 And txtZoekOpArtNr < 2000 Then
        txtZoekOpArtNr.BackColor = &HC0C0FF
        frmArtikels.Hide  'if form is not hidden, the focus will not set in txtZoekopArtNr
' In stead of msgbox, I call userform "frmBroodgetaltegroot"
'        MsgBox "In de afdeling 'Brood' is het laatste artikelnummer " & CellValue1 & "." & vbNewLine & _
'        "Het getal mag dus niet groter zijn dan " & CellValue1 & "!!", vbCritical, "Getal is groter dan " & CellValue1 & "!"
    frmBroodGetalteGroot.lblGetal = CellValue1 'Putting the max Nr in teh userform
    frmBroodGetalteGroot.lblGetal2 = CellValue1 'Putting for the second time the max Nr in teh userform
   
    frmBroodGetalteGroot.Caption = "Getal is groter dan " & CellValue1 & "!"  'Putting the title of the header of the userform with Max nr
    frmBroodGetalteGroot.Show 'show the error userform in stead of msgbox
    frmArtikels.txtZoekOpArtNr.SetFocus ' setting focus on the textbox
'    frmArtikels.txtZoekOpArtNr.SelStart = 0 'selecting value in textbox
    frmArtikels.txtZoekOpArtNr.SelLength = Len(.Text) 'selecting value in textbox
    frmArtikels.Show 'By clicking ok on the userform frmBroodGetalteGroot, the main userform is shown again
        txtZoekOpArtNr.BackColor = &HC0FFFF 'setting back the original color background
'        txtZoekOpArtNr.Value = ""
        lstboxArtGevonden.Clear 'making sure the listbox is cleared
        Exit Sub

Display More

In attachment my complete workbook.

Justin Doward · Aug 14th 2022

Hello AL,

The solution to your 2nd problem is here:

SetFocus not working on TextBox after AfterUpdate [SOLVED]

Essentially you need to disable the next textbox in the tab order before setting focus in your code:

Code

txtZoekOpArtBenaming.Enabled = False
txtZoekOpArtNr.SetFocus

then include a sub that re-enables the disabled textbox when setfocus occurs:

Code

Private Sub txtZoekOpArtNr_Enter()
txtZoekOpArtBenaming.Enabled = True
End Sub

This is working for me on the example you sent.

Please note that placing this second question after your first question is not helpful to the forum, particularly as you have not really shown the solution to your first question. No doubt a moderator will draw your attention to the rules and request you read them again.

NB: Good luck with the coding, I like the look of the forms. I would suggest the following if you are open to it:

1.

You are not always using your WITH statements, once you use a with statement you do not then need to name the object again.

2.

I suggest rewriting the code so that very little of it runs from the form code page, rather have code written in the modules and called from the form page. This generally allows you to write code that is more flexible and can be called from multiple locations.

3. you can loop through controls using for example:

Code

with frmKlanten
for i = 1 to 8
.controls("TextBox" & i).value = ""
next i
end with

Best

Justin

Allround_IT_er · Aug 14th 2022

Hello JD,

Thanks for your help, much appreciated!

The problem is solved. Thanks you so much.

And thanks for the tips regarding my code.

I still have a lot to learn!

I will try to change my WITH statments as explain by you. Don't know if I will succeed but I will give it a try

What do you mean by:

Quote

I suggest rewriting the code so that very little of it runs from the form code page, rather have code written in the modules and called from the form page. This generally allows you to write code that is more flexible and can be called from multiple locations.

Could you give me an example?

Thanks in advance

Justin Doward · Aug 15th 2022

Hi AL,

I have made a small example using your sheet and an alternative approach.

Note, I have only built the article number textbox and the listbox. I have used this site:

The VBA Guide To ListObject Excel Tables

Learn everything there is to know about manipulating and interacting with ListObjects in VBA. Excel Tables are a cornerstone of spreadsheet design. There are…

www.thespreadsheetguru.com

when learning to navigate tables in vba.

NB: As the thread is solved, send me direct if you have any further questions.

Regards

Justin

Error when entering a number in combobox that doesn't exist

Allround_IT_er Aug 15th 2022

Participate now!

Share

Similar Threads

Looping trough all workbooks in a folder an their worksheet. Action is to paste special, so that there is no formula

Help Disabling Controls On UserForm based on ComboBox In Another UserForm

Help with DateADD function

Users Viewing This Thread