+ Reply to Thread
Results 1 to 16 of 16

Can a macro be used to randomly choose drop down selections

  1. #1
    Registered User
    Join Date
    09-24-2014
    Location
    Auckland, New zealand
    MS-Off Ver
    2013
    Posts
    50

    Can a macro be used to randomly choose drop down selections

    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?

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,378

    Re: Can a macro be used to randomly choose drop down selections

    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?
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    09-24-2014
    Location
    Auckland, New zealand
    MS-Off Ver
    2013
    Posts
    50

    Re: Can a macro be used to randomly choose drop down selections

    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

  4. #4
    Registered User
    Join Date
    09-24-2014
    Location
    Auckland, New zealand
    MS-Off Ver
    2013
    Posts
    50

    Re: Can a macro be used to randomly choose drop down selections

    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

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,378

    Re: Can a macro be used to randomly choose drop down selections

    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.
    Last edited by Bernie Deitrick; 03-19-2018 at 04:57 PM.

  6. #6
    Registered User
    Join Date
    09-24-2014
    Location
    Auckland, New zealand
    MS-Off Ver
    2013
    Posts
    50

    Re: Can a macro be used to randomly choose drop down selections

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

  7. #7
    Registered User
    Join Date
    09-24-2014
    Location
    Auckland, New zealand
    MS-Off Ver
    2013
    Posts
    50

    Red face Re: Can a macro be used to randomly choose drop down selections

    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.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,378

    Re: Can a macro be used to randomly choose drop down selections

    Try it like this, attached:
    Attached Files Attached Files
    Last edited by Bernie Deitrick; 03-20-2018 at 08:46 AM.

  9. #9
    Registered User
    Join Date
    09-24-2014
    Location
    Auckland, New zealand
    MS-Off Ver
    2013
    Posts
    50

    Re: Can a macro be used to randomly choose drop down selections

    Thank you very much Bernie,

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

  10. #10
    Registered User
    Join Date
    09-24-2014
    Location
    Auckland, New zealand
    MS-Off Ver
    2013
    Posts
    50

    Re: Can a macro be used to randomly choose drop down selections

    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

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,649

    Re: Can a macro be used to randomly choose drop down selections

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  12. #12
    Registered User
    Join Date
    09-24-2014
    Location
    Auckland, New zealand
    MS-Off Ver
    2013
    Posts
    50

    Re: Can a macro be used to randomly choose drop down selections

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

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,649

    Re: Can a macro be used to randomly choose drop down selections

    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.

  14. #14
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,378

    Re: Can a macro be used to randomly choose drop down selections

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

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    09-24-2014
    Location
    Auckland, New zealand
    MS-Off Ver
    2013
    Posts
    50

    Re: Can a macro be used to randomly choose drop down selections

    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

  16. #16
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,378

    Re: Can a macro be used to randomly choose drop down selections

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. randomly choose cells for a specific input goal budget
    By happyandjeffswife in forum Excel General
    Replies: 2
    Last Post: 06-21-2015, 03:00 AM
  2. [SOLVED] Randomly choose one of several images to make visible, rest invisible.
    By Kramxel in forum PowerPoint Programing
    Replies: 1
    Last Post: 10-23-2013, 07:22 AM
  3. Can Solver choose randomly between two identical outcomes?
    By mattmars in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2013, 04:57 PM
  4. Define set of files and choose one randomly.
    By Bob@Sun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-28-2010, 10:57 AM
  5. How to randomly choose sample rows after using Autofilter??
    By Saarang84 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-08-2009, 03:43 AM
  6. [SOLVED] Randomly Choose Cells from Colum/Row
    By J in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-18-2005, 10:25 PM
  7. Macro to choose from filter drop down
    By The Grinch in forum Excel General
    Replies: 0
    Last Post: 02-22-2005, 08:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1