shuffle, reveal and magnify cell as cell is selected

Mark2017

New Member
Joined
Oct 17, 2017
Messages
3
Thank you in advance for any guidance to this difficult request :eeek:.
I have a list of 70 names in a column.
  • I need to randomize this list and hide the names (I'm thinking white font color matching the background).
  • Cell A1 would be blank. The starting position.
  • Using the Enter key or down arrow key; as focus changes to each selected cell the data would be revealed (font color change) and really magnified pretty much filling the screen.
  • As focus changes to the next cell the previous cell would be changed back to "hidden".

In essence, Only a single name is displayed or seen at a time on a large screen and the selected speaker is unknown until the name magically appears on the screen.

I hope this makes sense and again I thank each of you.

Mark
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Here's a different approach you might want to consider.

Put your randomized name list in B2:B71 and hide column B.
Merge cells C3:J23. Format horizontal alignment to CentreAcrossSelection and vertical alignment to Centre.

Put this in the sheet module :
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = [A2:A71]
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
    [C3].Value = Intersect(Target, rng)(1, 2)
Else
    [C3:J23].ClearContents
End If
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
End Sub
 
Last edited:
Upvote 0
If you want to randomize the name list each time a selection is made, then :

Merge cells D3:K23 (instead of C3:J23)
In C2:C71 enter =RAND()
Hide columns B & C
Put this in the sheet module :
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = [A2:A71]
If Target.Cells.Count <> 1 Then Exit Sub
Application.ScreenUpdating = False
If Not Intersect(Target, rng) Is Nothing Then
    rng.Offset(0, 1).Resize(, 2).Sort Key1:=[C2], _
        Order1:=xlAscending, Header:=xlNo
    [D3].Value = Intersect(Target, rng)(1, 2)
Else
    [D3:J23].ClearContents
End If
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
End Sub
 
Upvote 0
If you want to randomize the name list each time a selection is made, then :

footoo, is it possible to randomize the list and then stop without it continuing to randomize each time the selection changes? If so, can this be put into a macro so each meeting it can be randomized and then start right into the display portion? The display part is great ;)
 
Upvote 0
Set up like in post # 3 then :

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = [A2:A71]
If Target.Cells.Count <> 1 Then Exit Sub
Application.ScreenUpdating = False
If Target.Address = "$A$1" Then
    [D3:K23].ClearContents
    rng.Offset(0, 1).Resize(, 2).Sort Key1:=[C2], _
        Order1:=xlAscending, Header:=xlNo
ElseIf Not Intersect(Target, rng) Is Nothing Then
    [D3].Value = Intersect(Target, rng)(1, 2)
Else
    [D3:K23].ClearContents
End If
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
End Sub
This will randomize the names only if A1 is selected.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

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
Back
Top