VBA : UserForm : Generate a unique ID Reference no.

[RAKESH230583]

Hello Friends,

I have one userform used to collect periodic data, Wherein with the help of Application.Username I am able to extract the user details.

But is there any way that we can get the unique ID reference no. on every time the user form is filled and submitted by the user.

Post user save (Command tab) the userform - Message to show with Unique ID reference no . and then after user click on OK the same must be saved on Sheet1 (Data Dump) on Column B.

Example :
21032018001

21 - Date of submission
03 - Current Month
2018 - Current year
001 - Sequence number of data saved on that day.

 

[RAKESH230583]

Dear Friend - To Make is simple:

Can we create a unique ID reference number with the Help of VBA Code - Every time the user submit the User form and that ID gets auto captured in Column B.

Example :
21032018001

21 - Date of submission
03 - Current Month
2018 - Current year
001 - Sequence number of data saved on that day.

 

[vmjan02]

In how many sheets a data is been entered. Is it only 1 sheet or more than 1 sheet?

 

[vmjan02]

then below code will work,

Private Sub CommandButton1_Click()
Sheets("sheet1").Select ' all the data is captured in sheet1
Range("a1").Select
Selection.Formula = "=MAX(B1:B50000)"
Dim a As Integer
Sheets("Sheet1").Select
a = Sheets("sheet1").Range("a1").Value + 1
LastRowFulll = Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 2).Row 'counter added in cell b2
Sheet1.Cells(LastRowFulll, 2).Select ' next row and counter is been added.
ActiveCell.Value = a
End Sub

 

[RAKESH230583]

All the filled information of UserForm will be extracted only be in Sheet1 ("Data Dump")
From Column - A to M, Where Heading for B1 is Unique ref. No.
So when first set of data is filled in userform then, Sheet1 ("Data Dump") Cell B2 to evidence the Unique reference no, For Second set of Data, Sheet1 ("Data Dump") Cell B3 to evidence same for next...

 

[vmjan02]

Yes this code will work as you want. Just change the "sheet1" to "Data Dump"

Private Sub CommandButton1_Click()
Sheets("sheet1").Select ' all the data is captured in sheet1
Range("a1").Select
Selection.Formula = "=MAX(B1:B50000)"
Dim a As Integer
Sheets("Sheet1").Select
a = Sheets("sheet1").Range("a1").Value + 1
LastRowFulll = Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 2).Row 'counter added in cell b2
Sheet1.Cells(LastRowFulll, 2).Select ' next row and counter is been added.
ActiveCell.Value = a
End Sub

 

[RAKESH230583]

Hey Thanks - But above VBA Code only provide me the sequence number E.g. 1, 2, 3, 4 ..... under B2, B3, B4, B5 .....

Whereas request was to generate the unique reference no. with below format

Lets say users has submitted form 3 time today. i.e. 21 March 2018

On 1st Submission B2 = 2103201801
On 2nd Submission B3 = 2103201802
On 3rd Submission B4 = 2103201803

on day 2 - i.e. 22nd March 2018

On 1st Submission B5 = 2203201801
On 2nd Submission B6 = 2203201802
On 3rd Submission B7 = 2203201803

First 2 digit - Date of submission - Current date
Second 2 digit : Month of submission - Current Month
Third 4 digit - Year of submission - Current year
Fourth 2 digit - number of entry for that day i.e. 01, 02, 03 ...

 

[vmjan02]

Sheets("sheet1").Select ' all the data is captured in sheet1
Range("a1").Select
Selection.Formula = "=VALUE(TEXT(TODAY(),""ddmmyyyy""))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Formula = "=max(b1:b500000)"
Dim a As Double
Sheets("Sheet1").Select
a = Sheets("sheet1").Range("a1").Value + 1
LastRowFulll = Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 2).Row 'counter added in cell b2
Sheet1.Cells(LastRowFulll, 2).Select ' next row and counter is been added.
ActiveCell.Value = a

Use this...

 

[RAKESH230583]

Same the above code is not working as per the requirement and does show only sequence number - 1 ,2, 3 , 4 .....

 

[Raheem0102]

Dear Friend - To Make is simple:

Can we create a unique ID reference number with the Help of VBA Code - Every time the user submit the User form and that ID gets auto captured in Column B.

Example :
21032018001

21 - Date of submission
03 - Current Month
2018 - Current year
001 - Sequence number of data saved on that day.

i have methed
i search a lot but not find then i make for my self