Unique Print ID Number VBA

Raze4305

New Member
Joined
Apr 5, 2022
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.


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
 

Attachments

  • Screenshot 22.jpg
    Screenshot 22.jpg
    78.8 KB · Views: 19
  • Screenshot 2022-04-06 070912.jpg
    Screenshot 2022-04-06 070912.jpg
    18.3 KB · Views: 17

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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