TextBox Match

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
164
hi,
I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes 10, 21 & 22 will be visible or not which works ok, the problem I am having is the matchtext if they scan the correct codes in texboxes 10, 21 & 22 to match textbox1 (Label Code) then textbox11 should say Pass but I keep getting a Fail but it works when only scanning one label code (see pics below) ant help would be great.

VBA Code:
Private Sub TextBox8_Change()
If TextBox8.Value = 3 Then Exit Sub
If TextBox8.Value = 1 Then
TextBox21.Visible = False
TextBox22.Visible = False
Else: TextBox8.Value = 2
TextBox22.Visible = False
End If
End Sub

VBA Code:
Sub matchtext()
If TextBox1.Value = "" Or TextBox10.Value = "" Then Exit Sub
If TextBox10.Value + TextBox21.Value + TextBox22.Value = (TextBox1) Then
TextBox11 = ("PASS")
TextBox11.BackColor = vbGreen

Label20.Visible = True
Label21.Visible = True
TextBox16.Visible = True
TextBox17.Visible = True
TextBox18.Visible = True
TextBox19.Visible = True
Else
      TextBox11.BackColor = vbRed
    Application.Speech.Speak "FAIL"
TextBox11 = ("FAIL")
Dim sPath As String
result = MsgBox("THIS LABEL CODE DOES NOT MATCH THE PRICE SHEET", vbOKOnly + vbCritical, "WARNING")
If result = vbOK Then
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim wb As Workbook
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "WARNING" & vbNewLine & vbNewLine & _
"There has been a no match scanning error" & vbNewLine & vbNewLine & _
"LINE NUMBER: " & ComboBox4.Value & vbNewLine & _
"PRODUCT CODE: " & ComboBox1.Value & vbNewLine & _
"PRODUCT DESCRIPTION: " & TextBox2.Value & vbNewLine & _
"LABEL CODE ON PRICE SHEET: " & TextBox1.Value & vbNewLine & _
"LABEL CODE SCANNED: " & TextBox10.Value

On Error Resume Next
With xOutMail
'.To = "jo.blogs"
'.CC = ""
.Subject = "Stores label code scanning error"
.Body = xMailBody
.Attacments = ActiveSheet
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

TextBox10.Text = ""
TextBox11.Text = ""
    TextBox10.SetFocus
    TextBox11.BackColor = &H80000002

End If
End If
End Sub

1600163666270.png
1600164284314.png
1600164366134.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows
When doe the code run? When you click a button, enter something in a textbox, select a checkbox, or something else?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows
Do you mean it currently runs when you enter a value in a textbox?
If so which textbox?
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,912
I guess that, rather than If TextBox10.Value + TextBox21.Value + TextBox22.Value = (TextBox1) Then, you need
VBA Code:
If TextBox10.Value = TextBox1.Value And TextBox21.Value = TextBox1.Value And TextBox22.Value = TextBox1.Value Then
Bye
My updated proposal:
VBA Code:
If ((TextBox10.Value = TextBox1.Value) + (TextBox21.Value = TextBox1.Value) + (TextBox22.Value = TextBox1.Value)) = -(CLng(TextBox8.Value)) Then
Bye
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,023
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

textbox21
That won't work. If you only have one label, then that textbox will never have anything entered into it, so the macro won't get called. Also if you have 3 labels then the code will run after you have entered something into 2 of the textboxes & before the final one is filled.
You would be better off calling it from a command button.
 

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
164
Hi, I have found another way to make it work with the code below, is there a way of shortening the code some how ?

VBA Code:
Private Sub TextBox8_Change()
If TextBox8.Value = 3 Then Exit Sub
If TextBox8.Value = 1 Then
TextBox21.Visible = False
TextBox22.Visible = False
TextBox23.Visible = False
TextBox24.Visible = False
Else: TextBox8.Value = 2
TextBox23.Visible = False
TextBox24.Visible = False
End If
End Sub

VBA Code:
Private Sub TextBox10_AfterUpdate()
    TextBox10.Text = UCase(TextBox10.Text)
If TextBox1.Value = "" Or TextBox10.Value = "" Then Exit Sub
If TextBox1.Value = TextBox10.Value Then
TextBox11 = ("PASS")
TextBox10.BackColor = vbGreen
TextBox11.BackColor = vbGreen
Else
TextBox10.BackColor = vbRed
      TextBox11.BackColor = vbRed
    Application.Speech.Speak "FAIL"
TextBox11 = ("FAIL")
Dim sPath As String
result = MsgBox("THIS CODE DOES NOT MATCH THE PRICE SHEET", vbOKOnly + vbCritical, "WARNING")
If result = vbOK Then
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim wb As Workbook
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "WARNING" & vbNewLine & vbNewLine & _
"There has been a no match scanning error" & vbNewLine & vbNewLine & _
"PRODUCT CODE: " & ComboBox1.Value & vbNewLine & _
"PRODUCT DESCRIPTION: " & TextBox2.Value & vbNewLine & _
"LABEL CODE ON PRICE SHEET: " & TextBox1.Value & vbNewLine & _
"LABEL CODE SCANNED: " & TextBox10.Value

On Error Resume Next
With xOutMail
'.To = ""
'.CC = ""
.BCC = ""
.Subject = "Stores label code scanning error"
.Body = xMailBody
.Attacments = ActiveSheet
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

TextBox10.Text = ""
TextBox11.Text = ""
TextBox10.BackColor = &HFFFFFF
TextBox11.BackColor = &H80000002
TextBox10.SetFocus
End If
End If
End Sub

VBA Code:
Private Sub TextBox21_AfterUpdate()
    TextBox21.Text = UCase(TextBox21.Text)
If TextBox1.Value = "" Or TextBox21.Value = "" Then Exit Sub
If TextBox1.Value = TextBox21.Value Then
TextBox22 = ("PASS")
TextBox21.BackColor = vbGreen
TextBox22.BackColor = vbGreen

Else
TextBox21.BackColor = vbRed
      TextBox22.BackColor = vbRed
    Application.Speech.Speak "FAIL"
TextBox22 = ("FAIL")
Dim sPath As String
result = MsgBox("THIS CODE DOES NOT MATCH THE PRICE SHEET", vbOKOnly + vbCritical, "WARNING")
If result = vbOK Then
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim wb As Workbook
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "WARNING" & vbNewLine & vbNewLine & _
"There has been a no match scanning error" & vbNewLine & vbNewLine & _
"PRODUCT CODE: " & ComboBox1.Value & vbNewLine & _
"PRODUCT DESCRIPTION: " & TextBox2.Value & vbNewLine & _
"LABEL CODE ON PRICE SHEET: " & TextBox1.Value & vbNewLine & _
"LABEL CODE SCANNED: " & TextBox10.Value

On Error Resume Next
With xOutMail
'.To = ""
'.CC = ""
.BCC = ""
.Subject = "Stores label code scanning error"
.Body = xMailBody
.Attacments = ActiveSheet
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

TextBox21.Text = ""
TextBox22.Text = ""
TextBox21.BackColor = &HFFFFFF
TextBox22.BackColor = &H80000002
TextBox21.SetFocus
End If
End If
End Sub

VBA Code:
Private Sub TextBox23_AfterUpdate()
    TextBox23.Text = UCase(TextBox23.Text)
If TextBox1.Value = "" Or TextBox23.Value = "" Then Exit Sub
If TextBox1.Value = TextBox23.Value Then
TextBox24 = ("PASS")
TextBox23.BackColor = vbGreen
TextBox24.BackColor = vbGreen

Else
TextBox23.BackColor = vbRed
      TextBox24.BackColor = vbRed
    Application.Speech.Speak "FAIL"
TextBox24 = ("FAIL")
Dim sPath As String
result = MsgBox("THIS CODE DOES NOT MATCH THE PRICE SHEET", vbOKOnly + vbCritical, "WARNING")
If result = vbOK Then
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim wb As Workbook
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "WARNING" & vbNewLine & vbNewLine & _
"There has been a no match scanning error" & vbNewLine & vbNewLine & _
"PRODUCT CODE: " & ComboBox1.Value & vbNewLine & _
"PRODUCT DESCRIPTION: " & TextBox2.Value & vbNewLine & _
"LABEL CODE ON PRICE SHEET: " & TextBox1.Value & vbNewLine & _
"LABEL CODE SCANNED: " & TextBox10.Value

On Error Resume Next
With xOutMail
'.To = ""
'.CC = ""
.BCC = ""
.Subject = "Stores label code scanning error"
.Body = xMailBody
.Attacments = ActiveSheet
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

TextBox23.Text = ""
TextBox24.Text = ""
TextBox23.BackColor = &HFFFFFF
TextBox24.BackColor = &H80000002
TextBox23.SetFocus
End If
End If
End Sub

1600177015091.png
1600177077372.png
 

Watch MrExcel Video

Forum statistics

Threads
1,113,825
Messages
5,544,545
Members
410,619
Latest member
gregor222
Top