TextBox Match

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
172
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
 
When doe the code run? When you click a button, enter something in a textbox, select a checkbox, or something else?
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Do you mean it currently runs when you enter a value in a textbox?
If so which textbox?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top