PDA

View Full Version : Generate Unique Reference Numbers on a userform



dedmonds
09-20-2011, 04:55 AM
Hello,
I am wondering if anyone can suggest a way to generate a unique reference when saving the data on a userform to a workbook.

Cheers

Dave

Bob Phillips
09-20-2011, 05:03 AM
Look up Generate GUID on Google.

dedmonds
09-20-2011, 05:10 AM
Hi Thanks for that but it seems like overkill for what I need. I just want to assign a unique ref number to the row when writing it to the workbook.
EG 001, 002, 003

Cheers

Dave

Kenneth Hobs
09-20-2011, 06:35 AM
Put a value of say 1 in Sheet1!A2 and set the cell number custom format to: "EG" 000

Then run the sub. The values are actually numbers.

Sub SetNextNumber()
Dim theRange As Range, theCell As Range, nextLong As Long
Set theRange = Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp))
Set theCell = Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1)
nextLong = NextNumber(theRange)
theCell.Value = nextLong
theCell.NumberFormat = """EG"" 000"
End Sub

Function NextNumber(aRange As Range) As Long
NextNumber = WorksheetFunction.Max(aRange) + 1
End Function

mikerickson
09-20-2011, 07:32 AM
NOW will generate a unique number