+ Reply to Thread
Results 1 to 10 of 10

Cmd Button to populate all drop down list on a worksheet as "Yes"

  1. #1
    Registered User
    Join Date
    05-15-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    20

    Cmd Button to populate all drop down list on a worksheet as "Yes"

    Hello,

    I have a page with 30 drop down boxes, which populate from a drop down list with the values "", "Yes", "No", "N/A". I would like to incorporate a command button when pressed will populate all of the drop down questions as "Yes" so the user does not have to manually each box using the drop down list.

    I hope this makes sense.

    Thank you,

    Michael

  2. #2
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Cmd Button to populate all drop down list on a worksheet as "Yes"

    this code needs to be tailored to your situation (as far as i can tell from your description), it is easily doable

    can you post your workbook?

  3. #3
    Valued Forum Contributor MaczaQ's Avatar
    Join Date
    06-03-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003 / XP
    Posts
    510

    Re: Cmd Button to populate all drop down list on a worksheet as "Yes"

    Hi,

    If you have simply validation maybe will be fine only this line i.e.:
    Please Login or Register  to view this content.
    Best Regards
    MaczaQ
    ---------------------------------------------------------------------------------------------------------------------------
    If you are satisfied with the solution(s) provided, please mark your thread as Solved
    If you are pleased with my answer consider to rate it. To thank someone who has helped you, click on the star icon below their name.
    - This way you will add him some reputation points ... thanks in advance.

  4. #4
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

    Re: Cmd Button to populate all drop down list on a worksheet as "Yes"

    This this:

    Please Login or Register  to view this content.
    I use a variation of this to clear all controls on a userform, haven't tested it with just a worksheet though

    Sorry, just tested and the above code won't work on a sheet. Makes sense though since there's no form
    Last edited by VBA FTW; 01-28-2013 at 05:16 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Cmd Button to populate all drop down list on a worksheet as "Yes"

    MaczaQ has the right idea, let's hope that it's as simple a task at that, otherwise you will have to include each individual range that is affected

  6. #6
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

    Re: Cmd Button to populate all drop down list on a worksheet as "Yes"

    Quote Originally Posted by scott.s.fower View Post
    MaczaQ has the right idea, let's hope that it's as simple a task at that, otherwise you will have to include each individual range that is affected
    If the OP is using validation lists (sounds like he's using drop down lists though) that aren't in sequential order ie are not in range A1:A30, an easy option would be to select all of those cells on the worksheet with your mouse and name the range. Then you should be able to just reference that named range in the VBA code. Would be a lot less tedious than referencing each cell individually for what's it's worth

  7. #7
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Cmd Button to populate all drop down list on a worksheet as "Yes"

    let me know if this works for you...

    this code will look at the used range in your worksheet, it will then go through each cell to see if it contains the data validation (see notes within the code)
    and will change it to yes

    Please Login or Register  to view this content.
    Last edited by scott.s.fower; 01-28-2013 at 06:01 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Cmd Button to populate all drop down list on a worksheet as "Yes"

    VBA FTW,

    that's a good idea, unless they are all over the sheet, i think the macro i wrote would be the best method to avoid having to find all the cells with validation

  9. #9
    Registered User
    Join Date
    05-15-2010
    Location
    Texas
    MS-Off Ver
    Excel 2000
    Posts
    20

    Re: Cmd Button to populate all drop down list on a worksheet as "Yes"

    Thank you for all of your help!

    scott.s.fower- great solution, but I kept receiving a compile error, Im sure it was due to my error.

    MaczaQ- your solution worked, I had to manually enter the cell locations, but in the end a good and simple solution.

    Cheers to everyone, next drink on me.

    Michael

  10. #10
    Valued Forum Contributor MaczaQ's Avatar
    Join Date
    06-03-2011
    Location
    Poland
    MS-Off Ver
    Excel 2003 / XP
    Posts
    510

    Re: Cmd Button to populate all drop down list on a worksheet as "Yes"

    Hi again

    What about this (without manually enter the cell location)
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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