Can a macro be used to randomly choose drop down selections

[ChrisCD]

The pupose of this is to ask the user random questions. I have three drop down menus to do with music theory but for arguments sake say the first one is just even numbers up to 20, the second list is the choice of plus or minus and the third, the choice of half, quarter or whole (of the number in list one)

so random questions could be

10 plus a quarter of 10 = 12.5
or 18 minus half of 18 = 9

I have it working with manual selection from a table lookup but wondered if I could automate it so the user just has to click something like Next question that links to a macro. Is it possible?

[Bernie Deitrick]

Yes - anything is possible in Excel (well, almost anything).

You could use a formula that would change every time the workbook is recalculated, a macro that chooses a value from each of the lists - but how is "18 minus half of 18 = 9" a question?

[ChrisCD]

It is worded a bit clumsily I'm afraid due to the fact this equation was derived from some hyperthetical drop down lists but I wanted to use math rather than musical intervals as less people would be familiar with them. half of 18 is 9 so 18 - 9 = 9
I have three instances of this formula below working simultaneously on all lists with the press of F9 so I'm getting somewhere.
=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)

Haven't tried making a macro yet. You're right about excel... you can do almost anything

[ChrisCD]

I seem to have come up aginst an issue with the formula =INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)

Is there a way to suppress it from randomly selecting every time enter is pressed after the spreadsheet is altered? The reason being, the user types their answer to the question C plus a Major Third = but when they press enter the question changes. This is the point where I would rather the answer checking formula is activated to display correct or incorrect as it does in the manual version. It is necessary that the user can choose to randomly select the next question in their own time and after they have seen the result of the current question... right or wrong.

If this formula can't do it, do you know another way?

Thanks, Chris

[Bernie Deitrick]

Chris,

I'm guessing you don't really have numbers, so you need to have some sort of lookup table for the answers. For example, starting in column A

Note -whole -half -third -quarter +quarter +third +half +whole etc.....
A E Fsharp .... etc.

(I'm just making stuff up there, since I have no idea) - so the transformations that you use are in the first row, and the second through the rest of the rows are the answers for each of the tranformations. Then you could use a macro (assuming, say, that you have 10 notes in column A starting in A2, and 10 possible transformations (in cells B1 through K1)

So, when you have that table set up, try this macro, below. If you have problems setting it up, post back with the workbook that you are working with.

Please Login or Register  to view this content.

[ChrisCD]

Thanks Bernie, I will get back to you with the file
Chris

[ChrisCD]

Hello Bernie, thanks for the code. I have entered it into a vb editor in the file but I am sure I haven't done everything. I am not very familiar with vb script.

I have attached the file...
the user types their answer to the question, say: "Major third above C is" but when they press enter the question still
changes instead of getting the correct right or wrong message.

[Bernie Deitrick]

Try it like this, attached:

[ChrisCD]

Thank you very much Bernie,

This does the job and gives me a better insight into some vb code

[ChrisCD]

Hi Bernie, There is one issue with the version 4 that I have tried unsuccessfully to solve with VB code. That is that there are two formulas in cells I58 and I59, that do not refresh each time which is affecting most answers. I think I placed them way too far down the page. If you have the time it would be wonderful if you were to take another look at it?
Kind regards
Chris

[AliGW]

Your thread is marked as solved - I suggest you go back and mark it unsolved for now.

[ChrisCD]

Thanks Ali, have done so just now. Will Bernie still have gotten a notification of my message?

[AliGW]

I think so, yes, but I don't use E-mail notifications myself (I have them turned off), so he may be the same. However, by posting here, you have bumped the thread back up to the top of the list, so he should see it when he logs on.

[Bernie Deitrick]

Try adding a line ("Application.CalculateFull" ) to force a recalculation after the random values are chosen:

Please Login or Register  to view this content.

[ChrisCD]

Thanks Bernie,

It's working fine now. I greatly appreciate your help. Now I have my eye on some VBA tutorials at lynda.com

Chris

[Bernie Deitrick]

Great - once you get the basics, the best VBA practice is reading the messages here and challenging yourself to solve the problems. Start with the solved ones.