Generate Random Numbers That Are Only Used Once

[Justinian]

I have to create a spreadsheet where employees click on a number 1-41. Each number is in its own cell and each number is hyperlinked to another tab where the user is presented with a question they have to answer. What I am trying to do is use RANDBETWEEN to generate a random number between 1-41. Supposed I do that and the number I get is 5. I click on 5 and am taken to that tab where I have to answer the question associated with that tab. Once 5 is used, I want it to be excluded from all further RANDBETWEEN queries. Each time another number is used, that number needs to be left out so that at the end, all 41 numbers are generated and no more numbers will appear. Is this possible? I attached a copy of the dashboard with the numbers.

https://app.box.com/s/q34ael8r5rf5k4tt8gqpcd6zxn37cuzy

 

[trimmjens]

If you remove the hyperlinks and use this code instead. I think this is easier.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCell As Range

On Error Resume Next
If Not Intersect(ActiveCell, Range("A2:J6")) Is Nothing Then
    Set myCell = ActiveCell
    If myCell.Font.Color = RGB(255, 0, 0) Then MsgBox "Number " & myCell.Value & " is already taken.", vbOKOnly, "Not available": Exit Sub
    Worksheets(Worksheets("Sheet1").Range(myCell.Address).Text).Activate

    With myCell
        .Font.Color = RGB(255, 0, 0)
    End With

End If

End Sub

This will take you to the sheet with the same name as the cell you click.
This will also change the font color of the cell you click, and if you select the cell again
you will get a MsgBox telling you it's already taken.

It may come in handy to have a reset button. Place an ActiveX button on the sheet
and put this code to it:

Private Sub CommandButton1_Click()

With Sheets("Sheet1").Range("A2:J6")
    .Font.Color = 0
End With

Range("A1").Activate

End Sub

 

[Justinian]

I did what you suggested and nothing happens (see attached). I want the user to be able to select a number at random in one cell, say C13. They should be able to select 40 other random numbers between 1 and 41 without getting a duplicate.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://app.box.com/s/yxmkbd5zcnwep0d6frc8y3jlk6k0oofm

[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

[/FONT]

 

[trimmjens]

I saw that i missed something regarding the random number thing. Working on it now.

Are you sure that Design Mode is turned off? Also make sure that the range in the code corresponde to the range on the sheet.
It should work if you put the code in the right places in VBA.

 

[Justinian]

Design Mode was off. There are no macros to run and the range ends in J7 with number 41.

 

[Justinian]

Anyone else?