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.

 

[Raheem0102]

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.

Private Sub UserForm_Initialize()
Me.Label1.Caption = Format(Date, "ddmmyyyy") & Format(ThisWorkbook.Worksheets("Sheet1").Range("A3").Value + 1, "000")

Sheet1.Range("A1") = Date

If Sheet1.Range("A1") <> Sheet1.Range("A2") Then
Sheet1.Range("A3") = 0

End If
End Sub
Private Sub CommandButton1_Click()
Me.Label1.Caption = Format(Date, "ddmmyyyy") & Format(ThisWorkbook.Worksheets("Sheet1").Range("A3").Value + 2, "000")

Sheet1.Range("A3").Value = Sheet1.Range("A3") + 1
Sheet1.Range("A2") = Date
End Sub

Private Sub CommandButton2_Click()
Unload Me
UserForm1.Show

End Sub


this user form have 1 label which show the "Unique ID reference number" and one Command Button which use for "Save" for transection
after save button the "Unique ID reference number" will change automatically
23072023003
23 07 2023 003
the 23 is the Currant day
the 07 is the Currant month
the 2023 is the Currant year
the 003 is the Currant day transection which automatically 001 on next date