Reset random list-name generator

[jammoca]

I am using the following code in a userform (via a command button called "NextName") to display (in a Label called "StudentName" ) a student name randomly chosen from a list in column A .......

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

However, after making it's way through the list once, it won't proceed as it has exhausted the list.

I would like to change the code so it resets itself when it finishes going through the list.

So after passing through the list once, I'd like the list to be randomised again, and names to be permitted to continue being displayed in the Label.

 

[GlennUK]

Do you want the message "All students have been listed" to be displayed in between exhausting the list and starting again?

If so, just change

        If .Cells(1, 4).Value > .Cells(2, 4).Value Then MsgBox "All students have been listed": Exit Sub

to be:

        If .Cells(1, 4).Value > .Cells(2, 4).Value Then MsgBox "All students have been listed": Columns(3).ClearContents: Exit Sub