Make Random Numbers for Purchase Orders

[jammer12001]

Been searching around for a couple of hours and have not found exactly what I was looking for. I have a user form that can be opened to create a new purchase order. One of the field's (textbox1) needs to be automatically filled with a random 5 digit number (10000/99999) when you open the user form. I currently just use this:

Private Sub UserForm_Initialize()

TextBox1.Value = RandBetween(10000, 99999)

End Sub


The problem is once the workbook has been closed and re open I get double numbers. The purchase orders will be saved to another sheet (PO Data With PO number located in column A) and I was wondering how I could make sure the value did not already exist before it displays it in the box to fill out the purchase order. If it did exist just loop until it gets a value that doesn't? Maybe I'm going about this the wrong way.

I have read a lot about returning guaranteed random numbers but I can't get it to generate correct and return to the textbox automatically when I open the form.

Thanks in advance,
Ben

 

[Yard]

I guess a Do...While loop would work, just keep generating random numbers until you get one that doesn't already exist in your list.

More intriguing is your use of random numbers for purchase orders...why not simply sequential?

 

[jammer12001]

How would I make it show the value in that text box though? For some reason I cant get it to.

We have been a having a couple problems when we use quick books and it making sequential PO's. Sometimes people feel like they can use the next number in the list and then it turns out that they either forget to create it later or another person has already done the same thing. If we use randoms they are forced to call the office and get a PO every time because they never know what the number will be.

 

[Yard]

I just created a userform with a label (lblNum) and an OK button (cmdOK) and put this code into the userform's code module:

Option Explicit

Dim rngMatch As Range
Dim wksMatch As Worksheet
Dim lNum As Long

Private Sub cmdOK_Click()

With wksMatch
    .Cells(Rows.Count, rngMatch.Column).End(xlUp).Offset(1, 0).Value = lNum
End With
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim dMatch As Double

Set rngMatch = Sheet1.Range("A1:A90000")
Set wksMatch = rngMatch.Parent
dMatch = 1
Do While dMatch > 0
lNum = WorksheetFunction.RandBetween(10000, 99999)
On Error Resume Next
dMatch = 0
dMatch = WorksheetFunction.Match(lNum, rngMatch, 0)
Loop

Me.lblNum.Caption = lNum
End Sub

Thanks for the explanation as well!