Hi All
I try to print a label every time a new item is added to the Data Sheet. But my issue is that the unique pallet number is not created until the record is created. I still need to be able to reprint the label from the list box on the form as well, which is working well. I was hoping to use the count if function, we operate the Day and Month as the pallet id and a unique number, e.g. DDMM-Number= 0604-1. if I could count the DDMM every day it will put the Pallet Number back to one.
I try to print a label every time a new item is added to the Data Sheet. But my issue is that the unique pallet number is not created until the record is created. I still need to be able to reprint the label from the list box on the form as well, which is working well. I was hoping to use the count if function, we operate the Day and Month as the pallet id and a unique number, e.g. DDMM-Number= 0604-1. if I could count the DDMM every day it will put the Pallet Number back to one.
VBA Code:
'Add Button--------------------
Private Sub CommandButton4_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim last_Row As Long
last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'Empty Box Validations----------------------------------------------------------------------
If Me.ComboBox1 = "" Then
MsgBox "A Bit To Quick My Friend! Nëed To Add Product Champ", vbCritical
Exit Sub
End If
If Me.ComboBox2 = "" Then
MsgBox "A Bit To Quick My Friend! Nëed To Add Grower Champ", vbCritical
Exit Sub
End If
If Me.ComboBox3 = "" Then
MsgBox "A Bit To Quick My Friend! Nëed To Add Destination Champ", vbCritical
Exit Sub
End If
'Wight New Record To Data Sheet---------------------------------------------
sh.Range("A" & last_Row + 1).Value = "=Row()-1" 'Record ID
sh.Range("B" & last_Row + 1).Value = Now 'BD ID
sh.Range("C" & last_Row + 1).Value = Now 'Pallet ID
[COLOR=rgb(184, 49, 47)]sh.Range("D" & last_Row + 1).Value = "=Row()-1" 'Pallet Number [/COLOR]
sh.Range("E" & last_Row + 1).Value = Me.ComboBox1.Value 'Supplier
sh.Range("F" & last_Row + 1).Value = Me.ComboBox2.Value 'Grower
sh.Range("G" & last_Row + 1).Value = Me.ComboBox3.Value 'Product
sh.Range("H" & last_Row + 1).Value = Me.ComboBox4.Value 'Product Line
sh.Range("I" & last_Row + 1).Value = Me.ComboBox5.Value 'Destination
sh.Range("J" & last_Row + 1).Value = Me.TextBox3.Value 'Count
sh.Range("K" & last_Row + 1).Value = Me.TextBox1.Value 'Bin Wieght
sh.Range("L" & last_Row + 1).Value = Me.TextBox2.Value 'Temp
sh.Range("M" & last_Row + 1).Value = Me.TextBox6.Value 'Grower Pallet Number
sh.Range("N" & last_Row + 1).Value = Me.TextBox7.Value 'Lot#
sh.Range("O" & last_Row + 1).Value = Me.TextBox5.Value 'Note
sh.Range("P" & last_Row + 1).Value = Me.ComboBox6.Value 'Úser Name
sh.Range("Q" & last_Row + 1).Value = Now 'Time Stamp
'Refresh Data Table--------
Call Refresh_Data
'Print---------------------
Call RePrint_Data 'Wight to Print Sheet
Call Print_Lables 'Print Macro
'Clear Boxes------------------------------------------------------------------
Me.ComboBox1 = "" 'Supplier
Me.ComboBox2 = "" 'Grower
Me.ComboBox3 = "" 'Product
Me.ComboBox4 = "" 'Product Line
Me.ComboBox5 = "" 'Destination
Me.ComboBox6 = "" 'Úser Name
Me.TextBox1 = "" 'Bin Wieght
Me.TextBox2 = "" 'Temp
Me.TextBox3 = "" 'Count
Me.TextBox4 = "" 'Record Validation ID
Me.TextBox5 = "" 'Note
Me.TextBox6 = "" 'Grower Pallet Number
Me.TextBox7 = "" 'Lot
Me.TextBox8 = "" 'Pallet ID
Me.TextBox11 = "" 'Pallet NO:
'Refresh Data Table-----------------------------------------------------------
Call Refresh_Data
End Sub