Random number generator that doesn't change on refresh

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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 ?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
The code below goes into the userform module. It assumes you have a button named NextName and a label named StudentName.
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
 

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
I figured you might want that. {grin}

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

Watch MrExcel Video

Forum statistics

Threads
1,123,482
Messages
5,601,920
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top