Forum Discussion

NotSoFastEddie's avatar
NotSoFastEddie
Brass Contributor
Jun 05, 2024

What would you do? User needs to generate a random number and ideally not leave the worksheet

Hey Gang! I have a spreadsheet (see below) that requires the user to generate unique identifiers for the PCODE row and the BUNDLE IDENTIFIER row.  What I have them doing is clicking on another worksheet and clicking the appropriate button, then return to the original worksheet and paste the value into the cell.  The buttons are connected to a VBA routine that does the actual generation.

 

I have been struggling with how I can do this from within the same worksheet.  The worksheet consists of different row types (PCODE, BUNDLE IDENTIFIER, COMPONENT), as the user builds out their bundle.

 

Update - I have found a method, which has the formula embedded in the target cell.  The formula is connected to a VBA function.  This works however, I need to copy that value into subsequent rows, and if I drag it, the formula comes along, so I am forced to copy and paste values which is a tad clunky.

formula - =ConcatRandomNumberWithSuffix("B")

It will also be subject to changing the value if i refresh the spreadsheet which would be a disaster.

 

Sub GeneratePCODE_Click()
Dim randomNumber As Long
Dim formattedNumber As String
Dim finalString As String
Dim ws As Worksheet
Dim genPCODEBUNDLEID As Worksheet
Dim cell As Range
! FOR PCODE
' Set the worksheet location for the output variables
Set genPCODEBUNDLEID = ThisWorkbook.Sheets("Generate_PCODE_BUNDLEID")
Set cell = genPCODEBUNDLEID.Range("D9")

cell.Value = "" ' clear out the value in the spreadsheet
genPCODEBUNDLEID.Calculate ' recalculate the sheet
' Generate a random number between 1,000,000 and 9,999,998
randomNumber = Application.WorksheetFunction.RandBetween(1000000, 9999998)

' Format the number with leading zeros to make it a 7-digit number
formattedNumber = Format(randomNumber, "0000000")

' Concatenate the formatted number with "Q"
finalString = formattedNumber & "Q"


' Set the PCODE value
cell.Value = finalString
End Sub
Sub GenerateBUNDLEID_Click()
Dim randomNumber As Long
Dim formattedNumber As String
Dim finalString As String
Dim ws As Worksheet
Dim genPCODEBUNDLEID As Worksheet
Dim cell As Range
! FOR BUNDLEID
' Set the worksheet location for the output variables
Set genPCODEBUNDLEID = ThisWorkbook.Sheets("Generate_PCODE_BUNDLEID")
Set cell = genPCODEBUNDLEID.Range("D18")

cell.Value = "" ' clear out the value in the spreadsheet
genPCODEBUNDLEID.Calculate ' recalculate the sheet
' Generate a random number between 1,000,000 and 9,999,998
randomNumber = Application.WorksheetFunction.RandBetween(1000000, 9999998)

' Format the number with leading zeros to make it a 7-digit number
formattedNumber = Format(randomNumber, "0000000")

' Concatenate the formatted number with "B"
finalString = formattedNumber & "B"
cell.Value = finalString
End Sub

  • NotSoFastEddie 

    You might replace the lines

     

    Set genPCODEBUNDLEID = ThisWorkbook.Sheets("Generate_PCODE_BUNDLEID")
    Set cell = genPCODEBUNDLEID.Range("D9")

     

    in GeneratePCODE_Click with

     

    Set cell = ActiveCell

     

    and assign the GeneratePCODE_Click macro to a custom keyboard shortcut.

     

    Do the same for GenerateBUNDLEID_Click.

Resources