Serial sequence unique Bills of Lading (BOL) after each printing.

[Carl_upnorth]

I have looked across the different forums to find an instance of what I need to accomplish, but alas have come up null.

Situation:
We manually (with ink pen) fill out shipping bills of lading (BOL) each is a three-part form that comes from the print house sequenced with serial numbers, each three-part form has a unique serial.

I have created a form for excel that greatly simplifies the manual task, but it is unusable until I can get it to serial sequence each unique BOL with a unique serial. It would be useful in a department with 10 individuals at separate computers using the master file to open the BOL at their desk to use the InputBox to enter their ID #. The bills do not need to sequence incrementally from the previous days numbers due to the following logic.

Serial number format: 230560100; where 23 is the current year, 056 is the Julian date, 01 is the employee ID, 00 is the first three-part bill of the day (01 is the next) under their ID. Each employee enters their ID, and if they close the file and reopen later that day an InputBox asks what the previous bills' last two digits was (this input changes at midnight to 00 with the date). We do not save electronic copies of the bills, only paper copy is retained.

Found the following but do not know how to rewrite to accomplish the above.
***********************
this one from extendoffice.com
This one gives the pop-up window which is nice but do not want to number each copy of same in sequence.
******************

Sub IncrementPrint()
'updateby Extendoffice
    Dim xCount As Variant
    Dim xScreen As Boolean
    Dim I As Long
    On Error Resume Next
LInput:
    xCount = Application.InputBox("Please enter the number of copies you want to print:", "Kutools for Excel")
    If TypeName(xCount) = "Boolean" Then Exit Sub
    If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Then
        MsgBox "error entered, please enter again", vbInformation, "Kutools for Excel"
        GoTo LInput
    Else
        xScreen = Application.ScreenUpdating
        Application.ScreenUpdating = False
        For I = 1 To xCount
            ActiveSheet.Range("A1").Value = " Company-00" & I
            ActiveSheet.PrintOut
        Next
        ActiveSheet.Range("A1").ClearContents
        Application.ScreenUpdating = xScreen
    End If
End Sub

********************************************************
below: your oringinal post Aug 26, 2010
Like this as it numbers each seperate bill in sequence.

**********

Sub PrintJobs()
Dim i As Long, startnum As Long, lastnum As Long

    startnum = Application.InputBox("Enter the first job number to be printed", "Print Job Number", 1, , , , , 1)
    lastnum = Application.InputBox("Enter the last job number to be printed", "Print Job Number", 1, , , , , 1)

    For i = startnum To lastnum
        Range("B1").Value = i
        ActiveWindow.SelectedSheets.PrintOut copies:=2
    Next

End Sub

 

[mikenelena]

Hi Carl,

Take a look at this code. I'm not sure I have it exactly the way you want, but I think I'm close. It requires an employee to enter their employee ID number and the last BOL number and then returns the new BOL number in the format you requested. I guess I'm not sure how you are tracking BOL numbers through a workday. You mentioned that you don't save them digitally. How then do you avoid having to tell the program what the last BOL number was every time?

Sub BOLNumber()
  
    Dim intYear As Integer
    Dim strJulianDate As String
    Dim intEmployeeID As Integer
    Dim intPreviousNumber As Integer
    Dim strBOLNumber As String
  
    intYear = Right(Year(Now()), 2)
    strJulianDate = Format(DateDiff("d", DateSerial(Year(Now()), 1, 0), Now()) + 1, "000")
    strJulianDate = IIf(Len(strJulianDate) = 2, "0" & strJulianDate, strJulianDate) ' Add leading zero if necessary
  
    intEmployeeID = InputBox("Please enter your employee ID")
    intEmployeeID = Format(intEmployeeID, "00")
  
    If intEmployeeID > 0 Then
        intPreviousNumber = InputBox("What was the previous BOL number?")
      
        If intPreviousNumber > 0 Then
            strBOLNumber = CStr(intYear) & strJulianDate & Format(intEmployeeID, "00") & Format(intPreviousNumber + 1, "00")
        Else
            strBOLNumber = CStr(intYear) & strJulianDate & Format(intEmployeeID, "00") & "01"
        End If
      
        MsgBox "Your BOL number is: " & strBOLNumber
      
    Else
        MsgBox "Invalid employee ID!"
    End If
  
End Sub

 

[Peter_SSs]

@Carl_upnorth
Welcome to the MrExcel board!
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Carl_upnorth]

Peter, thank for the info.

 

[Carl_upnorth]

Hi Carl,

Take a look at this code. I'm not sure I have it exactly the way you want, but I think I'm close. It requires an employee to enter their employee ID number and the last BOL number and then returns the new BOL number in the format you requested. I guess I'm not sure how you are tracking BOL numbers through a workday. You mentioned that you don't save them digitally. How then do you avoid having to tell the program what the last BOL number was every time?

Sub BOLNumber()
 
    Dim intYear As Integer
    Dim strJulianDate As String
    Dim intEmployeeID As Integer
    Dim intPreviousNumber As Integer
    Dim strBOLNumber As String
 
    intYear = Right(Year(Now()), 2)
    strJulianDate = Format(DateDiff("d", DateSerial(Year(Now()), 1, 0), Now()) + 1, "000")
    strJulianDate = IIf(Len(strJulianDate) = 2, "0" & strJulianDate, strJulianDate) ' Add leading zero if necessary
 
    intEmployeeID = InputBox("Please enter your employee ID")
    intEmployeeID = Format(intEmployeeID, "00")
 
    If intEmployeeID > 0 Then
        intPreviousNumber = InputBox("What was the previous BOL number?")
     
        If intPreviousNumber > 0 Then
            strBOLNumber = CStr(intYear) & strJulianDate & Format(intEmployeeID, "00") & Format(intPreviousNumber + 1, "00")
        Else
            strBOLNumber = CStr(intYear) & strJulianDate & Format(intEmployeeID, "00") & "01"
        End If
     
        MsgBox "Your BOL number is: " & strBOLNumber
     
    Else
        MsgBox "Invalid employee ID!"
    End If
 
End Sub

.
Unless I am missing something, this won't put the information into a designated cell and cycle the serial to the next number. See images below of what I am attempting.

As far as tracking and saving, these BOL's are used for local yard and shuttling drivers to have a BOL to transfer loaded trailers across town legally. Hard copy has to be maintained for record so no need to double it up and waste server space.

Each employee after whomever logs in first will be using read only file from the Master as there is no need to save each BOL.

The first image shows employee one's first BOL of the day.
The second image shows employee one's second BOL of the day.
The third image shows employee two's second BOL of the day.
the last two digits need to sequence +1 after each time the file is printed, and cycle back to 00 at midnight.

I hope this clarifies what is being attempted here. And most likely a big ask.

Thank you.

 

[mikenelena]

Carl,

You are correct. This will NOT put the information into a designated cell and cycle the serial to the next number. That's the part I was missing. I'll take another look and see if I can finish it for you.

 

[mikenelena]

Carl,

This is possible, but probably not without storing at least a day's worth of BOL numbers somewhere. It could be on a hidden worksheet, etc. If employees only ever generated BOL numbers sequentially, there'd be no need. If employee 01 generates a BOL, then employee 02 generates a BOL, then employee 01 generates a 2nd, the code has to be able to "know" that employee 01 already generated one earlier. As it stands, the only available data the code can draw from would be the cell that holds the BOL number. Once that changes when a second employee generates, there's no longer any way for the code to "remember" that the first employee already generated a BOL earlier. Does that make sense? That's why you need to store these numbers someplace. You don't have to store the entire BOL, just the number. If you are OK with that, I'll try to knock the rest of this out for you.

 

[Carl_upnorth]

Carl,

This is possible, but probably not without storing at least a day's worth of BOL numbers somewhere. It could be on a hidden worksheet, etc. If employees only ever generated BOL numbers sequentially, there'd be no need. If employee 01 generates a BOL, then employee 02 generates a BOL, then employee 01 generates a 2nd, the code has to be able to "know" that employee 01 already generated one earlier. As it stands, the only available data the code can draw from would be the cell that holds the BOL number. Once that changes when a second employee generates, there's no longer any way for the code to "remember" that the first employee already generated a BOL earlier. Does that make sense? That's why you need to store these numbers someplace. You don't have to store the entire BOL, just the number. If you are OK with that, I'll try to knock the rest of this out for you.

Apologies for the late reply.

That will work if the BOL file is only a daily dataset file for the numbers.

Though each day is unique for the next 77 years, each BOL is unique each day based on employee, and on the whole completely traceable.

Employee 1 and 2 and 3 do not have to follow each other's sequence and where each left off, say 2 hours ago. The person who closed their workbook and reopens the Master File as read only, just reaches for their previous hard copy printed and enters the last number used in the pop-up. Each employee has the ability to make 100 BOL's each day, which is way more than needed for each. (00 to 99)