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 .......
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.
Code:
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.
Last edited by a moderator: