TextBox Match

J

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
 
Sort by date Sort by votes
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Do you mean it currently runs when you enter a value in a textbox?
If so which textbox?
 
Upvote 0
Anthony47 said:
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
Click to expand...
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
johnsonk said:
textbox21
Click to expand...
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
You must log in or register to reply here.

Similar threads

C
  • Question Question
Linking two sheets in different workbooks takes a long time even with only 500 rows of data. Is there a quicker, more efficient way to do this?
Replies
0
Views
158
chazrab
C
I
  • Solved
Show or Hide Textbox advice
Replies
8
Views
625
dmt32
D
E
  • Question Question
If Active cell = ASKWH and cell same row but offset 2 columns = Depots listed
Replies
0
Views
377
Eric Penfold
E
I
  • Solved
Clling a module process
2
Replies
11
Views
610
ipbr21054
I
M
  • Question Question
VBA multi email help
Replies
1
Views
918
John_w
J

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

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