VBA needed to select a random name

[jj66]

I'm trying to set up a button that selects a randomly select a name from a
list of 10 names. I guess I need some VBA to do this. Would anyone know what
it is?
I got as far as making select a random number into a cell then putting an IF
formula into another cell but it doesn't work.
I thought there must be an easier way.

 

[A.W.J. Ales]

JJ66,

You could use

=CHOOSE(RANDBETWEEN(1:10),"Name1","Name2",...,"Name10")

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Max]

One way:

Assume your list of 10 names is in Sheet1, A1:A10

Assign the sub below to a button
[from "Forms" toolbar] on any sheet

A random name from the list in Sheet1
will be generated in cell B1 of the sheet
with each click of the button

Sub RandomName()
' Generates a random pick from a list in Sheet1, in A1:A10
' and puts the value in cell B1 of the activesheet.
' Max .newusers 9 Feb '04

With Range("B1")
.FormulaR1C1 = _
"=OFFSET(Sheet1!RC[-1],RANDBETWEEN(0,9),0)"
.Value = Range("B1").Value
End With

End Sub

Note: RANDBETWEEN requires the Analysis Toolpak add-in
be installed and activated (via Tools > Add-ins)

 

[Max]

Just in case needed ... perhaps some steps to ease you in?

Press Alt + F11 to go to VBE

Click Insert > Module

Copy > Paste
everything within the dotted lines below
[from "begin vba" till "end vba"]
into the empty white space on the right side in VBE

---------begin vba----------
Sub RandomName()
' Generates a random pick from a list in Sheet1, in A1:A10
' and puts the value in cell B1 of the activesheet.
' Max .newusers 9 Feb '04

With Range("B1")
.FormulaR1C1 = _
"=OFFSET(Sheet1!RC[-1],RANDBETWEEN(0,9),0)"
.Value = Range("B1").Value
End With

End Sub
---------end vba----------

Press Alt + Q to exit and return to Excel

Now to trial-run the macro
---------------------------------
In any sheet with *cell B1 empty*
in the same book where you have the names listed in Sheet1, A1:A10
-----------------------------------------

Press Alt + F8
(this brings up the "Macro" dialog)

Click on the macro "RandomName" > Run
(or just double click on "RandomName")

A random name will be generated & placed in cell B1

Repeating the macro run will generate another random name in B1
which will overwrite the previous one
(Note that duplicate names are possible)

Alternatively, to run the macro,
draw a button on the sheet from the Forms Toolbar
(If necessary, activate the toolbar via View > Toolbars > Forms)

The Assign Macro dialog will pop-up automatically
[You can also right-click on an existing button > Assign Macro]

Select "RandomName" > OK

Rename the button as say, "Generate Random Name"

Each click on the button will run the macro,
with a random name generated in cell B1, as before.