title: Creating Unique ID Based on Date, and Number of Requests
body: <p>I have created a spreadsheet (Excel 2007) that will track orders over the course of a year. As part of that tracking, I need to generate a unique identifier based on the date, and the number of orders that arrive in a day. The count resets each day. </p>
  
  <p>For example;</p>
  
  <p>Request 1= Date: 02/02/16 -> ID #  20160202-01</p>
  
  <p>Request 2 = Date: 02/02/16 -> ID # 20160202-02</p>
  
  <p>Request 3 = Date: 02/03/16 -> ID # 20160202-01</p>
  
  <p>Due to the nature of the business, orders arrive in e-mail format, and are then entered into the spreadsheet via a userform. Within this userform, I have tried an If..Then statement to increment a variable (n) if the date of the request matches the previous entry. This works, but the value of (n) resets whenever the form is ran, meaning that my maximum value I can get is 2. </p>
  
  <p>I believe the fix lies in making (n) a global/public variable, but I do not know how to use a public variable to be accessed via a userform. Does anyone have any advice, or good references to fix this problem? Thank you in advance for your help.</p>
  
  <p>UPDATE: Thank you everyone for the quick feedback. I just had a colleague stop by, and on a whim, asked her if she had a fix for me. She informed me that a Static variable would be the way to fix this problem. This fix helped, but is not a perfect solution.  I'll post the relevant code for this to show the implemented fix.</p>
  
  <pre><code>Sub CommandButtonSubmitClose_Click()
   Static n As Integer
   Dim ordDate As Date
   Dim ordYear As Integer
   Dim txtYear As String
   Dim txtMonth As String
   Dim txtDay As String
   Dim txtCount As String
   Dim IDnum As String
   Dim prevRow As Long
   Dim LastRow As Long, ws As Worksheet
  
   'Define variables'
   prevRow = LastRow - 1
   txtYear = reqYear
   txtMonth = Format(month(reqDate), "00")
   txtDay = Format(day(reqDate), "00")  
  
  If ordDate = ws.Range("A" &amp; prevRow).Value Then
   n = n + 1
   Else                    'Determine daily count'
     n = 1
   End If
  
  
  txtCount = Format(n, "00")
  
  'Create ID Number'
  
    IDnum = " " &amp; txtYear &amp; "" &amp; txtMonth &amp; "" &amp; txtDay &amp; "-" &amp; txtCount &amp; ""
  </code></pre>
  
  <p>New Issues: As stated in the comments, the variable is not stored if the spreadsheet is closed. So if orders come in later in the day, the count is reset, creating duplicates that cause the whole thing to go haywire. Also, if there is a non-consecutive date in the mix, the generator does not work. </p>