instanceoftime
Board Regular
- Joined
- Mar 23, 2011
- Messages
- 103
How can I trigger the change (If I use change event I will only get the first digit of the UPC) (UPC's are varying lengths)
The following code works (with a manual search button) and searches spreadsheet for the UPC# (hopefully helpful to others)
The following code works (with a manual search button) and searches spreadsheet for the UPC# (hopefully helpful to others)
Code:
Private Sub txtbxUPCNumber_Change()
Dim NotFound As Integer
Dim arr As Variant
Dim I As Long
Dim str1 As String, str2 As String, str3 As String, str4 As String, str5 As String, str6 As String
NotFound = 0
ActiveWorkbook.Sheets("Items").Activate
Response = txtbxUPCNumber.Text
ItemNumber = Response
If Response <> False Then
With ActiveSheet
arr = .Range("A1:H" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
For I = 1 To UBound(arr)
If arr(I, 7) = Response Then
str1 = IIf(str1 = "", arr(I, 1), str1 & "|" & arr(I, 1))
str2 = IIf(str2 = "", arr(I, 2), str2 & "|" & arr(I, 2))
str3 = IIf(str3 = "", arr(I, 3), str3 & "|" & arr(I, 3))
str4 = IIf(str4 = "", arr(I, 4), str4 & "|" & arr(I, 4))
str5 = IIf(str5 = "", arr(I, 7), str5 & "|" & arr(I, 7))
str6 = IIf(str6 = "", arr(I, 8), str6 & "|" & arr(I, 8))
End If
Next
If str1 = "" Then
AddUPC
NotFound = 1
Else
Framed1.Visible = True
lbxItemNumber.List = Split(str1, "|")
lbxDescription.List = Split(str2, "|")
lbxCost.List = Split(str3, "|")
lbxDate.List = Split(str4, "|")
lbxUPC.List = Split(str5, "|")
lbxPrice.List = Split(str6, "|")
End If
End If
End Sub