Random cell selection accross cell range when clicked

0

I have a cell range c2:j5 containing some data in white font to appear hidden.

Its needed to randomly select a cell and make the contents of that cell visible.

When the cell is clicked it needs to make the cells contents invisible again and randomly select another cell and make the hidden contents of that cell appear.

Answer
Discuss

Discussion

Don't forget to Select the Answer that worked best for you please. This helps future visitors find the answer more easily.
don (rep: 1297) Aug 9, '17 at 2:54 am
Add to Discussion

Answers

0
Selected Answer

Please install this code in the code sheet of the worksheet on which you want the action. Note that the font color will be set, and a random selection made, when the sheet is activated. Change to another worksheet and back to see this action. After that the code does what you describe.

Option Explicit
    Const RangeAddress As String = "C2:J5"
    
Private Sub Worksheet_Activate()
    ShowRandomCell
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Rng As Range
    Dim CellId As Long
    Set Rng = Range(RangeAddress)
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        If Target.Font.Color = vbBlack Then ShowRandomCell
    End If
End Sub
Private Sub ShowRandomCell()
    Static PreviousId As Long
    Dim Rng As Range
    Dim CellId As Long
    
    Set Rng = Range(RangeAddress)
        Rng.Font.Color = vbWhite
        Do
            Randomize
            CellId = Int(Rnd * Rng.Cells.Count) + 1
        Loop While CellId = PreviousId
        
        With Rng.Cells(CellId)
            .Font.Color = vbBlack
        End With
        PreviousId = CellId
End Sub

You can change the constant 'RangeAddress' to match your needs.

Discuss

Discussion

Thanks it only seems to repeat the same patterns each time worksheet is opened and not randomizing differentley.
Can it be made to also play a 3 second wav file when worksheet is opened so after it plays it starts to make first random cell selection then on every click a different second wave file beeps.
Adam (rep: 4) Aug 7, '17 at 12:45 am
I have amended the code in my answer to randomize the seed value on which the random cell selection is based. I have also added code to prevent the same random number coming up twice in a row.
As for your musical ambitions, I don't do follow-ups. Ask a separate question. However, you might bear in mind that this forum is about Excel and playing of WAV files isn't, really.
Variatus (rep: 1108) Aug 7, '17 at 4:49 am
Works great. Thank you again.
Adam (rep: 4) Aug 7, '17 at 7:01 am
Add to Discussion
0

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Set TRange = Range("C2:J5")

If Not Application.Intersect(Target, TRange) Is Nothing Then

    TRange.Font.ColorIndex = 2

        randomCell1 = Int(Rnd * TRange.Cells.Count) + 1

    With TRange.Cells(randomCell1)
    .Select
    .Font.ColorIndex = 1
    End With

End If

End Sub

Put it into the worksheet where you want it to work (Alt + F11 then look to the left of the new window and double-click the worksheet that has the table of data and insert the code into the window that opens.)

Discuss

Discussion

Thanks. This is no working for me cant see on worksheet.
Adam (rep: 4) Aug 7, '17 at 1:23 am
You have to double-click in the desired area for it to work.
don (rep: 1297) Aug 7, '17 at 5:50 am
Thank you I appreciate it.
Adam (rep: 4) Aug 7, '17 at 10:22 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login