Need to generate Serial Numbers without duplicate on my userform upon initialize.

[silversong]

Hi!

This is my first question on this forum so I will try to be clear and specific as possible. I know it can get frustrating when you guys have to give solutions of unclear questions.

- My purpose


· I have a database of patients, and a userform allows me to manage this database, including adding new entries.
· Each patient needs to have a unique ID (which I call clinic number). Obviously the ID can not be duplicated.
· The ID follows this format C000000ID.
· Upon opening the userform, the ID will appear on Textbox1 prefilled.

- What I managed to achieve
· I managed to use a code to generate sequential ID. e.g. C000001ID, C000002ID, C000003ID etc..
·

With TextBox1        .Value = "C" & Format(Cells(Rows.Count, 1).End(xlUp).Row + 1, "000000") & "ID"
        .Enabled = False
    End With

· Everything worked great until I delete a specific record. Deleting a record can create a duplicate entry.
· e.g. After deleting C000002ID, the next generated ID will be C000003ID...which already exists.

- My sad sad temporary solution
·

Private Sub UserForm_Initialize()Dim ws As Worksheet, iRow As Long


Set ws = Worksheets("Data")


iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row


'Clinic ID Validation-------------------------
If WorksheetFunction.CountIf(ws.Range("A:A", ws.Cells(iRow, 1)), Me.TextBox1.Value) > 0 Then
 With TextBox1
        .Value = "C1" & Format(Cells(Rows.Count, 1).End(xlUp).Row + 1, "00000") & "ID"
        .Enabled = False
    End With
Else
With TextBox1
        .Value = "C" & Format(Cells(Rows.Count, 1).End(xlUp).Row + 1, "000000") & "ID"
        .Enabled = False
    End With
    End If

· Basically an If statement that check for a duplicate. In the event of a duplicate, the ID follows the format "C100000ID" instead of "C000000ID"

- What I require.
· All I require is to generate unique ID numbers following the format C000000ID. The "C" and "ID" needs to remain the same. the "000000" need to obviously change. They DO NOT have to be sequntial. I dont mind if they are random numbers...just no duplicate.

haha Phew I tried to make everything seem logical and consistent. Would be really really grateful if someone could help me with this as I am still very new to VB Excel.

Many thanks,
Kasun.

 

[Dryver14]

you could place the number in a cell out of the way then before you do the rest of the macro put a simple line like this in your code

Sub IncreaseNumber()

    Dim i As Long
    
    ActiveSheet.Range("AR1").Value = ActiveSheet.Range("AR1") + 1
     
End Sub

 

[skywriter]

Here's another possibility.
If you are familiar with named ranges, you can also add a name that refers to a value.
So for my example you would create a name and set the scope to workbook.
The name would be LastIDNumber.
If your current last ID is C000001ID, then in refers to you would type
="C000001ID" and save the name.
Then the code to create a new number would be the following.

Sub IncrementNewID()
Dim x As String
x = "C" & Format((Mid([LastIDNumber], 2, 6) + 1), "000000") & "ID"
Names("LastIDNumber").RefersTo = x
End Sub

To get the value of LastIDNumber to use in any other code just use the following.
[LastIDNumber]
Example:

Sub Test()
    MsgBox [LastIDNumber]
End Sub

Also if you type =LastIDNumber in any cell you can see what the current value of the name is. You can also go into the name manager and change it if you ever have an issue.

 

[silversong]

That is amazing!! Thank you so much guys. Skywriter, I used your solution and implemented the code as such:

Dim x As String
LastID = Range("A" & Rows.Count).End(xlUp).Valuex = "C" & Format((Mid([LastID], 2, 6) + 1), "000000") & "ID"
Me.TextBox16.Text = x

It works as I need. I was wondering if I can do an additional step. Could I implement a way to recycle ID's that I have been deleted?


I am thinking something like this may work:

1. Edit"delete" command button such that the ID number of the row to be deleted will first be saved in a new column "deletedIDs" on a separate sheet called "storage". The code for my delete button is:

Private Sub CommandButton3_Click() ' Delete Button   Dim sil As Long
   If ListBox1.ListIndex = -1 Then
   MsgBox "Choose an entry", vbExclamation
   Exit Sub
   End If
   If ListBox1.ListIndex >= 0 Then
    cevap = MsgBox("Entry will be deleted. ... Are you sure ?", vbYesNo)
If cevap = vbYes Then
   Sheets("Data").Range("A:A").Find(ListBox1.Text).Activate
sil = ActiveCell.Row
   Sheets("Data").Rows(sil).Delete
                     
        End If
        End If


 Call Main 'Progress Bar
       
For a = 1 To 7
Controls("textbox" & a) = ""
Next


ListBox1.List = Sheets("Data").Range("a2:l" & [A65536].End(3).Row).Value
TextBox14.Value = ListBox1.ListCount
End Sub

2. I could then edit the code above with a IF / THEN statement to say:

[COLOR=#0000FF]IF[/COLOR] column storage.deletedIDs is not empty [COLOR=#0000FF]THEN[/COLOR] 
use the last value in that column [COLOR=#0000FF]AND[/COLOR] delete that value from the storage.deleted IDs column.

[COLOR=#0000FF]ELSE[/COLOR]
Me.TextBox16.Text = x
[COLOR=#0000FF]End If

[/COLOR]

I think this logic should work. However, as I am new to VB Excel (literally 3 days) my coding knowledge is poor. I would really appreciate the help as I am trying to learn VB myself. :D

Thanks in advance!!

 

[skywriter]

Well another way would be if you have a gap in your sequence of ID numbers you could use End(xlDown) to jump to the cell just before the gap and write code to add a new number based on that and put it into the cell below.

If there are no gaps then it will just jump to the last used number create a number and put it in the cell below that.

See my example below the code.

This code would take you to cell A4 where you could use the number 3, add 1 to it to make a new number and write it to the cell below.

Once the gaps are filled in, the code will jump you down to A10. The only caution would be if A2 is blank the code will jump past that cell. You could write a line checking if A2 is blank or not.

If it is blank it means you first ID would be 1.

Sub Test()
    Range("A1").End(xlDown).Select
End Sub

Excel 2010

	A
ID's

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

</tbody>

Sheet1
​