Userform VBA Create Unique ID from values of several controls

[richard_a]

Hi Guys

I have a userform which collects data on 30 people (a department of employees) on a daily basis. I then have a command button which transfers all of the collected data to a table for longitudinal records.

At the moment the userform transfers fields such as date, company, department, name etc.

What I would like to do is populate a textbox (which will be hidden on the userform) with a unique ID (Named UnID*) for each person (record) made up of the values in some of the existing controls. I would like this code to run as an After_Update event linked to the final combobox they will select prior to data transfer.

What I have so far is this code below. But it's not correct and I don't know where to go from here

For i = 1 To 30
   Controls("UnID" & i).Value = Int(CDbl(DateBox.Value())) & Right(CompName.Value, 4) & Dep.Value & Left(("NameBox" & i), 3) & Right(("NameBox" & i), 3)
   Next i

Any pointers would be great, and apologies for what I'm sure are rookie errors in the attempted code

Thanks in advance,

Richard

 

[Norie]

Does this work?

For i = 1 To 30
   Me.Controls("UnID" & i).Value = Int(CDbl(DateBox.Value())) & Right(CompName.Value, 4) & Dep.Value & Left(Me.Controls("NameBox" & i).Value, 3) & Right(Me.Controls("NameBox" & i).Value, 3)
Next i