Random "Number" Generator From List

[Realjoshtodd]

I got a template for this Random Number Generator, but I would like it to pull a name from a list in my workbook. (The book and CD that the template came from allows for modifying.)

"Sheet1" auto populates a list of names in column A from Rows 2 to 32. I would like to have instead of going off the random number between boxes full from that list. I can update the userform to remove those options.

Attached is the code that is in the userform.

Option Explicit

Dim Stopped As Boolean

Private Sub Label1_Click()

End Sub

Private Sub StartStopButton_Click()
    Dim Low As Double, Hi As Double
    
    If StartStopButton.Caption = "Start" Then
'       validate low and hi values
        If Not IsNumeric(TextBox1.Text) Then
            MsgBox "Non-numeric starting value.", vbInformation
            With TextBox1
                .SelStart = 0
                .SelLength = Len(.Text)
                .SetFocus
            End With
            Exit Sub
        End If
        
        If Not IsNumeric(TextBox2.Text) Then
            MsgBox "Non-numeric ending value.", vbInformation
            With TextBox2
                .SelStart = 0
                .SelLength = Len(.Text)
                .SetFocus
            End With
            Exit Sub
        End If
        
'       Make sure they aren't in the wrong order
        Low = Application.Min(Val(TextBox1.Text), Val(TextBox2.Text))
        Hi = Application.Max(Val(TextBox1.Text), Val(TextBox2.Text))
        
'       Adjust font size, if necessary
        Select Case Application.Max(Len(TextBox1.Text), Len(TextBox2.Text))
            Case Is < 5: Label1.Font.Size = 72
            Case 5: Label1.Font.Size = 60
            Case 6: Label1.Font.Size = 48
            Case Else: Label1.Font.Size = 36
        End Select
        
        StartStopButton.Caption = "Stop"
        Stopped = False
        Randomize
        Do Until Stopped
            Label1.Caption = Int((Hi - Low + 1) * Rnd + Low)
            DoEvents ' Causes the animation
        Loop
    Else
        Stopped = True
        StartStopButton.Caption = "Start"
    End If
End Sub

Private Sub CancelButton_Click()
    Stopped = True
    Unload Me
End Sub


Private Sub UserForm_Terminate()
    Stopped = True
End Sub

 

[Rijnsent]

Hi Josh,
your question is quite cryptic, what are you trying to achieve? Have that userform display any of the values from that range (A2:A32) randomly? If you want that item in your sheet, you could use =OFFSET(A1,RANDBETWEEN(1,31),0) as a simple alternative. Does that help?
Koen