Search code examples
excelvbarandomuserform

Generate random code - mixed number & alphabet

I want to record user data using a userform.

enter image description here

In first textbox, I will insert user's name.
Second textbox is their ID.

In the third textbox I want to generate a 5 character ID/code (mixed number & alphabet) by clicking the 'Generate' button (but I have no idea what is the coding).

Once I click 'Add user', I would like the data to be populated in the Excel sheet. I would like to insert number 1, 2, 3... in Column A, today's date (when the user details added) in Column B. Followed by the data added in the Userform in Column C, D & E.

Here is what I want the data to look like:

data example

Here is code I copied from the site.

Private Sub CommandButton2_Click()

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

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a Name number
If Trim(Me.TextBox1.Value) = "" Then
    Me.TextBox1.SetFocus
    MsgBox "Please complete the form"
    Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.TextBox3.Value

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox1.SetFocus

End Sub
Solution

  • This will generate a five character random string of numbers and letters. You could assign to your button and output to your form rather than a message box.

    Sub x()

Dim vOut(1 To 5), i As Long, n As Long

Randomize

For i = 1 To 5
    n = WorksheetFunction.RandBetween(1, 2)
    If n = 1 Then
        vOut(i) = WorksheetFunction.RandBetween(0, 9)
    Else
        vOut(i) = Chr(64 + WorksheetFunction.RandBetween(1, 26))
    End If
Next i

MsgBox Join(vOut, "")

End Sub