Barcodes by box in Excel. Compete all the items for one box before going to the next.
Free templates and templates with code are available for purchase for $50 USD
www.easyexcela...
Addition Templates may be available on request
Click this link to check out my one-on-one training www.calendly.co...
For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
I am able to provide online help on your computer at a reasonable rate.
You can help and generate a translation to you own language
www.youtube.com...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
How to insert VBA code in Excel • How to insert VBA code...
Private Sub TextBox1_Change()
On Error Resume Next
Dim r, erow As Long
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Me.ListBox1.Clear
For r = 5 To erow
If Sheet1.Cells(r, 2).Value = Me.TextBox1.Text Then
With Me.ListBox1
.AddItem Sheet1.Cells(r, 1).Value
.List(.ListCount - 1, 1) = Sheet1.Cells(r, 1).Value
End With
End If
Next r
Me.TextBox2.SetFocus
End Sub
Private Sub TextBox2_Change()
If Len(TextBox2.Text) = 6 Then
Call other
Else
Exit Sub
End If
End Sub
Private Sub UserForm_Initialize()
UserForm1.TextBox1.Value = boxnum
End Sub
Sub green()
Dim barcode As String
Dim rng, rng2 As Range
Dim rownumber As Long
Dim boxnum As String
'Dim erow As Long: erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
If Sheet1.Cells(2, 2) not equal "" Then
barcode = Sheet1.Cells(2, 2)
Set rng = Sheet1.Columns("a:a").Find(What:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, searchorder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
MsgBox "Not Found"
Exit Sub
Else
rownumber = rng.Row
Sheet1.Range(Cells(rownumber, 1), Cells(rownumber, 2)).Select
Selection.Interior.ColorIndex = 4
boxnum = Cells(rownumber, 2).Value
UserForm1.TextBox1.Value = boxnum
With UserForm1
.Width = 285
.Height = 300
.Show
End With
UserForm1.TextBox1.Value = boxnum
Set rng2 = Cells(rownumber, 2)
If boxnum not equal Cells(rownumber, 2).Offset(1, 0).Value Then
Cells(rownumber, 2).Offset(-1, 0).Select
Do While ActiveCell.Value = boxnum
Set rng2 = Union(rng2, ActiveCell)
ActiveCell.Offset(-1, 0).Select
Loop
If rng2.Interior.ColorIndex = 4 Then
Sheet2.Range("B2").Value = Cells(rownumber, 3).Value
Sheet2.Activate
Sheet2.Range("A1:C4").Select
ActiveSheet.PageSetup.PrintArea = "A1:C4"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Sheet1.Activate
End If
End If
Sheet1.Cells(2, 2) = ""
End If
Sheet1.Cells(2, 2).Select
End If
End Sub
Sub other()
Dim barcode As String
Dim rng, rng2 As Range
Dim rownumber As Long
Dim boxnum As String
Dim Form As Object
Set Form = UserForm1
barcode = UserForm1.TextBox2.Text
Set rng = Sheet1.Columns("a:a").Find(What:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, searchorder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
MsgBox "Not Found"
UserForm1.TextBox2.Text = ""
Exit Sub
Else
rownumber = rng.Row
UserForm1.TextBox2.Text = ""
Sheet1.Range(Cells(rownumber, 1), Cells(rownumber, 2)).Select
Selection.Interior.ColorIndex = 4
boxnum = Cells(rownumber, 2).Value
Set rng2 = Cells(rownumber, 2)
If boxnum not equal Cells(rownumber, 2).Offset(1, 0).Value Then
Cells(rownumber, 2).Offset(-1, 0).Select
Do While ActiveCell.Value = boxnum
Set rng2 = Union(rng2, ActiveCell)
ActiveCell.Offset(-1, 0).Select
Loop
If rng2.Interior.ColorIndex = 4 Then
Unload Form
Set Form = Nothing
Sheet2.Range("A3").Value = Cells(rownumber, 3).Value
Sheet2.Range("A2").Value = Cells(rownumber, 2).Value
Sheet2.Activate
Sheet2.Range("A1:C4").Select
ActiveSheet.PageSetup.PrintArea = "A1:C4"
'ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Sheet1.Activate
End If
End If
Sheet1.Cells(2, 2) = ""
End If
Sheet1.Cells(2, 2).Select
End Sub
Негізгі бет Barcodes by box in Excel
Пікірлер: 5