Generate unique code based on user form entry and put in table

[edgarmalroy]

Hello all!

I hope you can help. I have created a userform for data entry and is working well. I am hoping that I can add something that generates a unique code for each entry and add that.

So I have a table like below and a userform that populates everything from name onwards. I would like each submission on the form to also generate a unique client code, ideally based on the first letter +1 of the name and the number of row, as per below - is this possible? Many thanks

Client Code	Name	Start Date	Client Type	Psychotherapist
N001	Martin Stevenson	34/23/4234	x	x
C002	Bob Mortimer	34/23/123	x	x

Current code:

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Clients")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1



With ws

.Cells(iRow, 2).Value = Me.txtname.Value
.Cells(iRow, 3).Value = Me.txtdate.Value
.Cells(iRow, 4).Value = Me.cbtype.Value
.Cells(iRow, 5).Value = Me.cbtherapist.Value
.Cells(iRow, 6).Value = Me.txtfee.Value
.Cells(iRow, 7).Value = Me.txtphone.Value
.Cells(iRow, 8).Value = Me.txtemail.Value
.Cells(iRow, 9).Value = Me.txtadd1.Value
.Cells(iRow, 10).Value = Me.txtadd2.Value
.Cells(iRow, 11).Value = Me.txtcity.Value
.Cells(iRow, 12).Value = Me.txtpostcode.Value
.Cells(iRow, 13).Value = Me.txtgp.Value
.Cells(iRow, 14).Value = Me.cbhealth.Value
.Cells(iRow, 15).Value = Me.txthdetails.Value
.Cells(iRow, 16).Value = Me.txtemerg.Value
.Cells(iRow, 17).Value = Me.cbmeds.Value
.Cells(iRow, 18).Value = Me.txtmdetails.Value



End With

'clear the data
Me.txtname.Value = ""
Me.txtdate.Value = ""
Me.cbtype.Value = ""
Me.cbtherapist.Value = ""
Me.txtfee.Value = ""
Me.txtphone.Value = ""
Me.txtemail.Value = ""
Me.txtadd1.Value = ""
Me.txtadd2.Value = ""
Me.txtcity.Value = ""
Me.txtpostcode.Value = ""
Me.txtgp.Value = ""
Me.cbhealth.Value = ""
Me.txthdetails.Value = ""
Me.txtemerg.Value = ""
Me.cbmeds.Value = ""
Me.txtmdetails.Value = ""

End Sub

 

[Micron]

Result is N016, cell values are "Name" and 16.

 

[edgarmalroy]

I'm sorry, I realise now that I have made this needlessly complicated, but having seen responses really I have changed my mind on what I am after.

I hope it isn't too annoying to start again, let me know if you'd prefer a fresh post.

So if that table is this, is it possible to produce a random two letters followed by three digit row number like below, when I click button with the following code:

Client Code	Name	Start Date	Client Type	Psychotherapist
GF001
LP002
PP003
ZX004

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Clients")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1




With ws

  .Cells(iRow, 2).Value = Me.txtname.Value
  .Cells(iRow, 3).Value = Me.txtdate.Value
  .Cells(iRow, 4).Value = Me.cbtype.Value
  .Cells(iRow, 5).Value = Me.cbtherapist.Value
  .Cells(iRow, 6).Value = Me.txtfee.Value
  .Cells(iRow, 7).Value = Me.txtphone.Value
  .Cells(iRow, 8).Value = Me.txtemail.Value
  .Cells(iRow, 9).Value = Me.txtadd1.Value
  .Cells(iRow, 10).Value = Me.txtadd2.Value
  .Cells(iRow, 11).Value = Me.txtcity.Value
  .Cells(iRow, 12).Value = Me.txtpostcode.Value
  .Cells(iRow, 13).Value = Me.txtgp.Value
  .Cells(iRow, 14).Value = Me.cbhealth.Value
  .Cells(iRow, 15).Value = Me.txthdetails.Value
  .Cells(iRow, 16).Value = Me.txtemerg.Value
  .Cells(iRow, 17).Value = Me.cbmeds.Value
  .Cells(iRow, 18).Value = Me.txtmdetails.Value



End With

'clear the data
Me.txtname.Value = ""
Me.txtdate.Value = ""
Me.cbtype.Value = ""
Me.cbtherapist.Value = ""
Me.txtfee.Value = ""
Me.txtphone.Value = ""
Me.txtemail.Value = ""
Me.txtadd1.Value = ""
Me.txtadd2.Value = ""
Me.txtcity.Value = ""
Me.txtpostcode.Value = ""
Me.txtgp.Value = ""
Me.cbhealth.Value = ""
Me.txthdetails.Value = ""
Me.txtemerg.Value = ""
Me.cbmeds.Value = ""
Me.txtmdetails.Value = ""



End Sub

 

[Micron]

NP with continuing here AFAIC. My approach might be to generate 2 random numbers between 65 and 90 and get their associated Chr() values (A to Z) and concatenate the formatted row number as I've already done. Is it still OK if you duplicate pairs, as in AA, BB, etc.? Also OK if you get BD016 and BD025 because the entire value is still unique?

 

[edgarmalroy]

NP with continuing here AFAIC. My approach might be to generate 2 random numbers between 65 and 90 and get their associated Chr() values (A to Z) and concatenate the formatted row number as I've already done. Is it still OK if you duplicate pairs, as in AA, BB, etc.? Also OK if you get BD016 and BD025 because the entire value is still unique?

Yes to both questions. This sounds good, I'm sorry I'm still at a very basic stage. So would that be something like

.Cells(iRow, 1).Value = 'the random number + format

 

[Micron]

add these in the declarations section
Dim i As Integer
Dim strPrefix As String

Put this after you create iRow

Randomize
i = Int((90 - 65 + 1) * Rnd + 65)
strPrefix = Chr(i)
Randomize
i = Int((90 - 65 + 1) * Rnd + 65)
strPrefix = strPrefix & Chr(i)

This line should create the value in iRow, column 1

With ws
    .cells(iRow, 1) = strPrefix & format(.Cells(iRow,"000")

EDIT - wait, last line is wrong. Give me a few minutes.

 

[Micron]

.Cells(iRow, 1) = strPrefix & format(.Cells(iRow,1).Row, "000")
Again, I can only substitute and test within my immediate window. Using that format I get

?format(sheets("009").Cells(9,2).row,"000")
009

 

[Micron]

Random generator lines could go in a loop, but it would only save one line of code. I would loop if you change your mind about the number of letters in the prefix.

 

[edgarmalroy]

This works wonderfully, thank you so much

 

[Micron]

Glad I could help. If you have a solution it is customary to mark the post that contains it as the solution. This takes it off of the list of unsolved threads.