Auto select in a drop down box

**aidang** · 02-23-2007, 03:25 AM

Hi.

I have a drop down box using data validation, and I'm currently designing staff rosters. I want to be able to let Excel randomly select a value in the drop down box.

Eg.
If i have
FRANK SMITH
FRED LONG
KATIE KIT
in a drop down box, can i let excel automatically select one of those names?

thanks for your help in advance!
aidan g

**mrice** · 02-24-2007, 06:12 PM

Try this...

Sub Demo()
MyList = Range("A1").Validation.Formula1
MyArray = Split(MyList, ",")
MyChoice = Int((UBound(MyArray) + 1) * Rnd())
Range("A1") = MyArray(MyChoice)
End Sub

assuming validated cell is A1 and you have no commas in your possible validated values.

**aidang** · 02-25-2007, 01:36 AM

Hey,

Sorry to be a bit of a nuisance, but could you possibly apply the formula in a sheet and show me how t works... I'm a little confused.

Thanks for this! your a life saver!

aidan g.

**mrice** · 02-25-2007, 02:38 PM

Its a macro, not a formula.

Hit alt F11, right click on the explorer tree to add a module and paste the code in there.

Then back on the worksheet, hit tools-macros-run to run the macro.

**Teethless mama** · 02-26-2007, 12:27 AM

Originally Posted by aidang

Hi.

I have a drop down box using data validation, and I'm currently designing staff rosters. I want to be able to let Excel randomly select a value in the drop down box.

Eg.
If i have
FRANK SMITH
FRED LONG
KATIE KIT
in a drop down box, can i let excel automatically select one of those names?

thanks for your help in advance!
aidan g

Assuming name list in A2:A10
B1: holds data validation list

Data Validation
Allow:List
Source:=INDEX($A$2:$A$10,RANDBETWEEN(1,10))
to random select name by select the drop down of validation list

**aidang** · 03-01-2007, 02:56 AM

thanks for all your suggestions...

with the macro, if i had commas, what would it do?

thnx... (sorry for being a pain!

)

**aidang** · 03-01-2007, 03:02 AM

hey.

im sorry mrrice, but im completely lost. i dont get it... could you break it down for dumb people like me plz?

thnx for all ur help...
aidan.

**mrice** · 03-01-2007, 03:44 PM

Here's the macro inserted - hit Alt F11 to see it in module1

**textexpress** · 12-18-2013, 09:05 PM

Mrice. I stumbled upon this thread. I'm trying to do a similar thing. Can you help me?

**arlu1201** · 12-19-2013, 01:42 AM

textexpress,

Welcome to the forum.

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.