I need a random number within a range, and not to duplicate the numbers in current list

Hi all! Here is what I am doing. Our students have a alternate student number which is what they use for email, computer login, etc... the syntax is the first four numbers are 1010 and the last four are the variable...for example 10102342 is one student and 10102393 could be a another student. The range is 10100001-10109999. I currently have just under 1000 students with 1010#'s already, so I would like to create a button on a form that would generate a 1010xxxx number without duplicating the current numbers.

screenshot.png

Thanks in advance!!

My form has a text box (txtGenerate) which is the output; where the 1010# should show up. A command button (cmdGenerate) which is where the user will interact and click the button to generate the 1010#..

jmeddaugh,

Welcome to the forum!
Why a textbox? A user can manually change its contents. I think it would be better if you used a label. So assuming you replace the textbox with a label named lblGenerate, something like this snippet should work for you:

Private Sub cmdGenerate_Click()
    
    Dim lID As Long
    
    Do
        lID = Int(Rnd() * 9999) + 10100001
        If Columns("B").Find(lID, , xlValues, xlWhole) Is Nothing Then Exit Do
    Loop
    
    Me.lblGenerate.Caption = lID
    
End Sub

Could I create a button to insert the data into the spreadsheet. I could create text boxes for SchoolName, Fname, LName, GR and maybe a check box for "is currently enrolled"?
I don't want to ask for tooo much, but would you know the code to insert that data in the current spreadsheet??

Would I have to change the lblGenerate to a textbox so the data can be inputed into spreadsheet?
Other than that I have:
txtFname - Last Name
txtLname - First Name
txtGrade - Grade Level
chkEnroll - checkbox for enrolled
cmdSubmit - submit to spreadsheet (assuming where the code will go)

jmeddaugh,

It would probably look something like this:

Sub cmdWriteToSheet()
    
    Dim sEnrollmentStatus As String
    
    Select Case Me.chkEnrolled.Value
        Case True:  sEnrollmentStatus = "Currently Enrolled"
        Case Else:  sEnrollmentStatus = "Not Currently Enrolled"
    End Select
    
    Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 7).Value = Array(Me.txtSchoolName.Text, _
                                                                         Me.lblGenerate.Caption, _
                                                                         Me.txtStudentNumber.Text, _
                                                                         Me.txtFName.Text, _
                                                                         Me.txtLName.Text, _
                                                                         Me.txtGR.Text, _
                                                                         sEnrollmentStatus)
    
    Me.txtSchoolName.Text = vbNullString
    Me.lblGenerate.Caption = vbNullString
    Me.txtStudentNumber.Text = vbNullString
    Me.txtFName.Text = vbNullString
    Me.txtLName.Text = vbNullString
    Me.txtGR.Text = vbNullString
    Me.chkEnrolled.Value = False
    
    Me.txtSchoolName.SetFocus
    
End Sub

jmeddaugh,

In your cmdGenerate_Click code, specifically this line:

lID = Int(Rnd() * 9999) + 10100001

Change the 9999 to 8999