Show error if no match then clear textbox

[johnsonk]

Hello All,

I have a userform with 2 texboxes where I scan a barcode in both and if they match the textboxes turn green and red if no match, I am trying to show an error message if there if no match
then clear both textboxes but I can not figure out how to do it as I am very new to VBA, could anyone help.
Below is what I have at the minute.

Private sub TextBox2_Change ()
If TextBox5.Text = TextBox2.Text then
TextBox2.Backcolor = RGB(51, 255, 51)

TextBox5.Backcolor = RGB(51, 255, 51)
Else

TextBox2.Backcolor = RGB(255, 0, 0)
TextBox5.Backcolor = RGB(255, 0, 0)
End If
End Sub

Regards​

​

 

[DanteAmor]

Still no joy when i scan in to textbox5 the text appears and thats it no background colour or error message on fail. Does all the code need to be in the userform or some in a module?

You can try on a new userform with 2 textbox: textbox2 and textbox5.
Capture the textbox2 and then scan in textbox5.
Please, try and tell me.

Private Sub TextBox5_Change()    
    Call matchText
End Sub


Sub matchText()
    If TextBox2.Value = "" Or TextBox5.Value = "" Then Exit Sub
    If TextBox5.Value = TextBox2.Value Then
        TextBox2.BackColor = RGB(51, 255, 51)
        TextBox5.BackColor = RGB(51, 255, 51)
    Else
        TextBox2.BackColor = RGB(255, 0, 0)
        TextBox5.BackColor = RGB(255, 0, 0)
        MsgBox "no match"
        TextBox2.Value = ""
        TextBox5.Value = ""
        TextBox2.Setfocus 
    End If
End Sub

The code goes inside the userform

 

[johnsonk]

Hi DanteAmor,
Please see below links to show userform. Form1 shows blank userform, Form2 shows populated once product has been selected from combobox, Form3 shows all filled out ready to submit, once submit is pressed all the information is transferred to another sheet Form4 (PriceSheet).

https://www.dropbox.com/s/xl0906y4zf...Form1.png?dl=0
https://www.dropbox.com/s/np2alcbx08r51xy/Form2.png?dl=0
https://www.dropbox.com/s/yey4s4ggmh250yb/Form3.png?dl=0
https://www.dropbox.com/s/yz3tspdmmtmeb7d/Form4.png?dl=0

Here is the code I have in the userform very messy I know I will try and tidy it up once all working.

Private Sub ComboBox1_DropButt*******()
Dim i As Long, Lastrow As Long
Lastrow = Sheets("PriceSheet").Range("B" & Rows.count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 2 To Lastrow
Me.ComboBox1.AddItem Sheets("PriceSheet").Cells(i, "B").Value
Next i
End If
End Sub
Private Sub ComboBox1_Change()
Dim i As Long, Lastrow As Long
Lastrow = Sheets("Pricesheet").Range("B" & Rows.count).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("PriceSheet").Cells(i, "B").Value = (Me.ComboBox1) Or _
Sheets("PriceSheet").Cells(i, "B").Value = Val(Me.ComboBox1) Then
Me.TextBox1 = Sheets("PriceSheet").Cells(i, "C").Value
Me.TextBox5 = Sheets("PriceSheet").Cells(i, "A").Value
Me.TextBox4 = Sheets("PriceSheet").Cells(i, "M").Value
TextBox2.SetFocus
End If
Next
End Sub
Private Sub ComboBox6_DropButt*******()
Dim i As Long, Lastrow As Long
Lastrow = Sheets("Database").Range("F" & Rows.count).End(xlUp).Row
If Me.ComboBox6.ListCount = 0 Then
For i = 2 To Lastrow
Me.ComboBox6.AddItem Sheets("Database").Cells(i, "F").Value
Next i
End If
End Sub
Private Sub ComboBox5_DropButt*******()
Dim i As Long, Lastrow As Long
Lastrow = Sheets("Database").Range("D" & Rows.count).End(xlUp).Row
If Me.ComboBox5.ListCount = 0 Then
For i = 2 To Lastrow
Me.ComboBox5.AddItem Sheets("Database").Cells(i, "D").Value
Next i
End If
End Sub
Private Sub ComboBox3_DropButt*******()
Dim i As Long, Lastrow As Long
Lastrow = Sheets("Database").Range("H" & Rows.count).End(xlUp).Row
If Me.ComboBox3.ListCount = 0 Then
For i = 2 To Lastrow
Me.ComboBox3.AddItem Sheets("Database").Cells(i, "H").Value
Next i
End If
End Sub
Private Sub ComboBox3_Change()
Dim i As Long, Lastrow As Long
Lastrow = Sheets("Database").Range("H" & Rows.count).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("Database").Cells(i, "H").Value = (Me.ComboBox3) Or _
Sheets("Database").Cells(i, "H").Value = Val(Me.ComboBox3) Then
Me.TextBox4 = Sheets("Database").Cells(i, "I").Value
End If
Next
End Sub
Private Sub ComboBox2_DropButt*******()
Dim i As Long, Lastrow As Long
Lastrow = Sheets("Database").Range("K" & Rows.count).End(xlUp).Row
If Me.ComboBox2.ListCount = 0 Then
For i = 2 To Lastrow
Me.ComboBox2.AddItem Sheets("Database").Cells(i, "K").Value
Next i
End If
End Sub
Private Sub ComboBox4_DropButt*******()
Dim i As Long, Lastrow As Long
Lastrow = Sheets("Database").Range("K" & Rows.count).End(xlUp).Row
If Me.ComboBox4.ListCount = 0 Then
For i = 2 To Lastrow
Me.ComboBox4.AddItem Sheets("Database").Cells(i, "K").Value
Next i
End If
End Sub
Private Sub TextBox5_Change()
Call matchText
End Sub
Sub matchText()
If TextBox2.Value = "" Or TextBox5.Value = "" Then Exit Sub
If TextBox5.Text = TextBox2.Text Then
TextBox2.BackColor = RGB(51, 255, 51)
TextBox5.BackColor = RGB(51, 255, 51)
Else
TextBox2.BackColor = RGB(255, 0, 0)
TextBox5.BackColor = RGB(255, 0, 0)
MsgBox "no match"
TextBox2.Value = ""
TextBox5.Value = ""
TextBox2.BackColor = RGB(255, 255, 255)
TextBox5.BackColor = RGB(255, 255, 255)
End If
End Sub
Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim Lastrow As Long, ws As Worksheet
Set ws = Sheets("PackData")
Lastrow = ws.Range("A" & Rows.count).End(xlUp).Row + 1 'Finds the last blank row
ws.Range("B" & Lastrow).Value = TextBox1.Text
ws.Range("D" & Lastrow).Value = TextBox2.Text
ws.Range("F" & Lastrow).Value = TextBox4.Text
ws.Range("C" & Lastrow).Value = TextBox5.Text
ws.Range("A" & Lastrow).Value = ComboBox1.Text
ws.Range("E" & Lastrow).Value = ComboBox2.Text
ws.Range("G" & Lastrow).Value = ComboBox4.Text
ws.Range("H" & Lastrow).Value = ComboBox5.Text
ws.Range("I" & Lastrow).Value = ComboBox6.Text
ws.Range("J" & Lastrow).Value = CheckBox1.Value
Application.DisplayAlerts = True
Columns("J").Select
Selection.Replace What:="True", Replacement:="OUT", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
CheckBox1.Value = False
Range("K" & Rows.count).End(xlUp).Offset(1).Value = Now
Unload UserForm7
End Sub
Private Sub CommandButton2_Click()
Unload UserForm7
End Sub​

 

[johnsonk]

Done on a new userform when i did a match the boxes turned red and cleared but worked perfect for a fail

 

[DanteAmor]

Hi DanteAmor,
Please see below links to show userform. Form1 shows blank userform, Form2 shows populated once product has been selected from combobox, Form3 shows all filled out ready to submit, once submit is pressed all the information is transferred to another sheet Form4 (PriceSheet).

https://www.dropbox.com/s/xl0906y4zf...Form1.png?dl=0
https://www.dropbox.com/s/np2alcbx08r51xy/Form2.png?dl=0
https://www.dropbox.com/s/yey4s4ggmh250yb/Form3.png?dl=0
https://www.dropbox.com/s/yz3tspdmmtmeb7d/Form4.png?dl=0

Here is the code I have in the userform very messy I know I will try and tidy it up once all working.


​

Did you do the test as I mentioned in post #11 ?

Download my test file

https://www.dropbox.com/s/ls088ozdoualnau/uf match.xlsm?dl=0

 

[johnsonk]

See post #13

 

[DanteAmor]

See post #13

Has it worked on your userform?

 

[johnsonk]

No when I choose from the combobox and it populates in to textbox2 then I scan in to textbox5 the text appears but nothing happens if I remove the code below and choose from combobox the no match error message pops up

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]If TextBox2.Value = "" Or TextBox5.Value = "" Then Exit Sub[/FONT]

 

[DanteAmor]

No when I choose from the combobox and it populates in to textbox2 then I scan in to textbox5 the text appears but nothing happens if I remove the code below and choose from combobox the no match error message pops up

If TextBox2.Value = "" Or TextBox5.Value = "" Then Exit Sub

What is the difference between the new form code and your userform code?
The names of the textbox2 and textbox5 controls exist in your userform?
When you select an item in the combo, check if there are blank spaces in the textbox2, or if there are blank spaces in the textbox5.


If you do not find the problem, you could upload your file to the cloud to review it.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[johnsonk]

DanteAmor

See below link for workbook
https://www.dropbox.com/s/jl32m04fkggeojk/TEST.xlsm?dl=0

 

[DanteAmor]

DanteAmor

See below link for workbook
https://www.dropbox.com/s/jl32m04fkggeojk/TEST.xlsm?dl=0

I can not download the file. There is an error, apparently they are more than 30 megabytes.
You can send a file only with your userform and some data, just to check textbox 2 and 5.