Generate random alphanumeric code

[mikeha_99]

Hello,
I would like to generate a random 5 character code. The random letters should be caps, and numbers between 0-9. An example might be: "F7B4K"

Ideally, at least one character in the result would be a letter.

I look forward to any and all suggestions.

Thank you,
Mike

 

[lejoyeux3]

You sent me a lot of code. But why not just post the part of the code that's not working here in this post, so that I am not the only one working on it.

I tried it but not working as I am only allowed to use 5000 characters. I also wanted t attach the excel doc so that you can see it for ourself but it is not possible t do so here. Do you haveany other solution for that?

Hereis what I want to achieve:

1- I have a dashboard with to button: One will call a form and the other onefor admin password (that one works fine).

2- I have a sheet called "Product Code List" with 3 columns in it.One for "Product Code", another one for "Product Name" anda last one for "Requested By"

3- The Form works as follow:

We have 2 Text boxes (One for Product Name & One for the Requestor ID) thena button to generate the unique random alphanumeric Product Code. Oncegenerated, all values should be added to thesheet.
- All fields are mandatory when using the form and for a given"Product Code" should correspond to a "Product Name" and a"Requestor ID".
- Product Code should be unique, Product Name should be unique.
- The code in the form, before adding the values in sheet"Product Code List" should make a look up and see if Product Codeor Name already exist in the database
- If it does not exist then add the 3 values in the form to in theDatabase "Product Code List"

So basically, the code you received is meant to perform that task.

But I am not able to insert the code or the excel doc.

Can you assist?

 

[lejoyeux3]

Private Sub Generate_Product_Code_Click()
'Sub UniqueCodes()
Dim LengthOfCode As Long, strCodeChars As String, uniquecode As String
Dim x As Long, r As Long
'*************** [START] Trying to Append another on this ***************
Dim sProductCode As String
Dim sProductName As String
Dim sRequestedBy As String
'Dim lngUnitStartRow As Long, lngUnitStartColumn As Long, lngUnitLastRow As Long
'Dim lngProductNameStartRow As Long, lngProductNameStartColumn As Long, lngProductNameLastRow As Long
Dim lngLastRow As Long, i As Long
'All inputs are mandatory. In case of missing inputs prompt user and exit.
If Product_Name.Text = "" Then
    MsgBox "All fields are mandatory - Please enter Product Name."
    Exit Sub
End If
If My_ID.Text = "" Then
    MsgBox "All fields are mandatory - Please enter your  ID."
    Exit Sub
End If
'Determine last row with data in Product Code List database
lngLastRow = shProductCodeList.Cells(shProductCodeList.Rows.Count, "A").End(xlUp).Row
'Search if the Product Code generated and Product Name already exists. If it exists prompt user and exit. If it does not exist, add it to database.
Set rngProductCode = shProductCodeList.Range(shProductCodeList.Cells(2, 1), shProductCodeList.Cells(lngLastRow, 1)).Find(sProductCode)
If Not rngProductCode Is Nothing Then
    Product_Code.Text = sProductCode
    MsgBox "Product Code " & sProductCode & " already exists in database."
    Exit Sub

Set rngProductName = shProductCodeList.Range(shProductCodeList.Cells(2, 2), shProductCodeList.Cells(lngLastRow, 1)).Find(sProductName)
If Not rngProductName Is Nothing Then
    Product_Name.Text = sProductName
    MsgBox "Product Name " & sProductName & " already exists in database."
    Exit Sub
Else
    Product_Code.Text = sProductCode
    shProductCodeList.Cells(lngLastRow + 1, 1) = sProductCode
    shProductCodeList.Cells(lngLastRow + 1, 2) = Product_Name.Text
    shProductCodeList.Cells(lngLastRow + 1, 3) = _ID.Text
    
    
End If
'***************
LengthOfCode = 12
strCodeChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
With CreateObject("scripting.dictionary")
For r = 2 To r
    Do
            For x = 1 To LengthOfCode
                uniquecode = uniquecode & Mid(strCodeChars, Application.RandBetween(1, 36), 1)
            Next
            If Not (.exists(uniquecode)) Then .Add uniquecode, 1: Cells(r, 3) = uniquecode: uniquecode = "":
                        
            Exit Do
            
    Loop
    
Next
End With
    
'***************
If lngLastRow > 1 Then
    'Format Product Code List Table.
    shProductCodeList.Visible = xlSheetVisible
    shProductCodeList.Select
    shProductCodeList.Range(shProductCodeList.Cells(2, 1), shProductCodeList.Cells(lngLastRow + 1, 6)).Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ThemeColor = 10
            .TintAndShade = -0.249946592608417
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ThemeColor = 10
            .TintAndShade = -0.249946592608417
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ThemeColor = 10
            .TintAndShade = -0.249946592608417
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ThemeColor = 10
            .TintAndShade = -0.249946592608417
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ThemeColor = 1
            .TintAndShade = -0.499984740745262
            .Weight = xlHairline
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ThemeColor = 1
            .TintAndShade = -0.499984740745262
            .Weight = xlHairline
        End With
        
        With Selection.Font
            .Name = "Calibri Light"
            .Size = 9
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        
    'Make alternate rows appear in different color for making it easier to read data
        For i = 2 To lngLastRow + 1
            If i Mod 2 = 1 Then
            shProductCodeList.Range(shProductCodeList.Cells(i, 1), shProductCodeList.Cells(i, 6)).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent2
                    .TintAndShade = 0.799981688894314
                    .PatternTintAndShade = 0
                End With
            End If
        Next i
    shProductCodeList.Visible = xlSheetVeryHidden
End If
'*************** [END] ***************

End If
MsgBox "Product Code " & UCase(sProductCode) & " successfully added to database."
End Sub