สามารถทำได้แล้วครับแต่ติดปัญหาคือ
- เวลา scan เลยข้อมูล ที่กำหนดไว้เรื่อยๆ Msg จะขึ้นหลัง ข้อมูลบันทึกไปแล้วครับ
- ผมยากให้ Yes คือทำต่อ และ No คือยกเลิก ผมต้องแก้ยังไงหรอครับ
Code: Select all
Private Sub TextBox2_AfterUpdate()
If Me.TextBox2.Text = "" Then Exit Sub
emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
With Worksheets("Barcode")
If Me.TextBox2.Text = "10021" Then
If MsgBox(" µéͧ¡Ò÷ӵèÍËÃ×ÍäÁè ", vbYesNo + vbQuestion + vbDefaultButton2, " Close and Save ") = 6 Then
.Range("a" & .Rows.Count).End(xlUp).Resize(, 9).Delete shift:=xlUp
End If
End If
ListBox1.RowSource = Sheets("Barcode").Range("A2:I1048576").Address(external:=True)
.Range("m2").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K2"), Range("I2:I500"))
.Range("m3").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K3"), Range("I2:I500"))
.Range("m4").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K4"), Range("I2:I500"))
.Range("m5").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K5"), Range("I2:I500"))
.Range("m6").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K6"), Range("I2:I500"))
.Range("m7").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K7"), Range("I2:I500"))
.Range("m8").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K8"), Range("I2:I500"))
.Range("m9").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K9"), Range("I2:I500"))
.Range("m10").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K10"), Range("I2:I500"))
.Range("m11").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K11"), Range("I2:I500"))
ListBox2.RowSource = Sheets("Barcode").Range("J2:M1048576").Address(external:=True)
If Me.TextBox2.Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Sheets("Barcode").Range("K2:N50"), Me.TextBox2.Value) = 0 Then
Call Sample2
'Me.TextBox2.Value = ""
MsgBox "Not found."
Exit Sub
End If
With Me
.TextBox4 = Application.WorksheetFunction.VLookup(CLng(Me.TextBox2), Sheets("Barcode").Range("K2:N50"), 4, 0)
End With
.Cells(emptyrow, 1).Value = TextBox1.Value 'date
.Cells(emptyrow, 2).Value = ComboBox3.Value 'factory
.Cells(emptyrow, 3).Value = TextBox5.Value 'stlye
.Cells(emptyrow, 4).Value = TextBox3.Value 'colors
.Cells(emptyrow, 5).Value = ComboBox4.Value 'size
.Cells(emptyrow, 6).Value = ComboBox2.Value 'note
.Cells(emptyrow, 7).Value = ComboBox1.Value 'name
.Cells(emptyrow, 8).Value = TextBox2.Value 'barcode
.Cells(emptyrow, 9).Value = TextBox4.Value 'number
.Range("m2").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K2"), Range("I2:I500"))
.Range("m3").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K3"), Range("I2:I500"))
.Range("m4").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K4"), Range("I2:I500"))
.Range("m5").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K5"), Range("I2:I500"))
.Range("m6").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K6"), Range("I2:I500"))
.Range("m7").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K7"), Range("I2:I500"))
.Range("m8").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K8"), Range("I2:I500"))
.Range("m9").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K9"), Range("I2:I500"))
.Range("m10").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K10"), Range("I2:I500"))
.Range("m11").Value = WorksheetFunction.SumIf(Range("H2:H500"), Range("K11"), Range("I2:I500"))
For Each Rng In Range("l2:l11")
If Rng.Offset(0, -1).Value = CLng(Me.TextBox2.Text) And Rng.Value < Rng.Offset(0, 1).Value Then
If MsgBox(" µéͧ¡Ò÷ӵèÍËÃ×ÍäÁè ", vbYesNo + vbQuestion + vbDefaultButton2, _
" Save and close ") = 6 Then
Call Sample2
MsgBox "TOTAL NUMBER ERROR"
Exit Sub
End If
End If
Next Rng
Dim strRowSource As String
With ListBox2
strRowSource = .RowSource
.RowSource = vbNullString
.RowSource = strRowSource
End With
Dim lsRow As Long
With Sheets("Barcode")
lsRow = .Range("a" & .Rows.Count).End(xlUp).Row
End With
ListBox1.RowSource = Sheets("Barcode").Range("A2:i" & lsRow).Address(external:=True)
With ListBox1
.ListIndex = .ListCount - 1
.Selected(.ListCount - 1) = True
End With
End With
End Sub
Code: Select all
For Each Rng In Range("l2:l11")
If Rng.Offset(0, -1).Value = CLng(Me.TextBox2.Text) And Rng.Value < Rng.Offset(0, 1).Value Then
If MsgBox(" µéͧ¡Ò÷ӵèÍËÃ×ÍäÁè ", vbYesNo + vbQuestion + vbDefaultButton2, _
" Save and close ") = 6 Then
Call Sample2
MsgBox "TOTAL NUMBER ERROR"
Exit Sub
End If
End If
Next Rng
You do not have the required permissions to view the files attached to this post.
สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ 
โปรดทราบ 
