Generate Random Numbers That Are Only Used Once

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,473
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

trimmjens

New Member
Joined
Oct 26, 2012
Messages
44
If you remove the hyperlinks and use this code instead. I think this is easier.

Code:
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:

Code:
Private Sub CommandButton1_Click()

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

Range("A1").Activate

End Sub
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,473
Office Version
  1. 365
Platform
  1. Windows
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]
 
Last edited:

trimmjens

New Member
Joined
Oct 26, 2012
Messages
44
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

Well-known Member
Joined
Aug 9, 2009
Messages
1,473
Office Version
  1. 365
Platform
  1. Windows
Design Mode was off. There are no macros to run and the range ends in J7 with number 41.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

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