Random number generator that doesn't change on refresh

[jammoca]

I have a Userform with a command button that when pressed is to result in the display of a randomly selected student name (the student list is found in column A) of sheet 1.

The name will be displayed in a Text Box also found on the Userform.

Once a student name has been displayed once, it should not be permitted to reappear again.

Is there some code I might be able to use for the command button and TextBox button ?

 

[tusharm]

The code below goes into the userform module. It assumes you have a button named NextName and a label named StudentName.

Option Explicit

Sub Initialize(aWS As Worksheet)
    With aWS
    Dim aRng As Range
    Set aRng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
        End With
    With aRng
    .Copy
    .Offset(0, 2).PasteSpecial xlPasteValues
    With .Offset(0, 3)
    .FormulaR1C1 = "=RAND()"
    .Value = .Value
        End With
    .Offset(0, 2).Resize(, 2).Sort Key1:=.Offset(0, 3).Cells(1, 1), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    .Offset(0, 3).EntireColumn.Delete
    With .Offset(0, 3)
    .Cells(1, 1).Value = 0
    .Cells(2, 1).Value = aRng.Cells.Count
        End With
        End With
    End Sub
Private Sub NextName_Click()
    Dim aWS As Worksheet
    Set aWS = ActiveSheet
    With aWS
        If IsEmpty(.Cells(1, 3).Value) Then Initialize aWS
        .Cells(1, 4).Value = .Cells(1, 4).Value + 1
        If .Cells(1, 4).Value > .Cells(2, 4).Value Then MsgBox "All students have been listed": Exit Sub
        Me.StudentName.Caption = .Columns(3).Cells(.Cells(1, 4).Value)
        End With
    End Sub

 

[jammoca]

Thankyou, so much ... that worked an absolute treat.

At the moment, when it has completed going through the student list once, it won't allow me to start going through the list a second time.

I will be needing to use this same student list more than once ( as the class will be performing more than one performance ).

Is there a way to code it, so that once the list has been completed the first time, the list is randomised a second time, and the button can continue to work its way through the list again, and again and again etc.

Any help would be greatly appreciated.

 

[tusharm]

I figured you might want that. {grin}

Whenever you want to redo the list delete (or clear the contents of) columns C and D.