+ Reply to Thread
Results 1 to 15 of 15

VBA code to populate data related to drop down list

  1. #1
    Registered User
    Join Date
    10-21-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    7

    VBA code to populate data related to drop down list

    Hi All,

    I am new to this thread.

    I need to populate data in 5 cells based on the data of a dropdown list.
    If I select Home in the dropdown then data in next 5 cells should display Home details of the person.
    If I select Office in the dropdown then data in next 5 cells should display Office details of the person.

    Could any one please help me with the hint of the code that I can use.

    Thanks,
    Sunil
    Last edited by ExcelGyan; 10-26-2010 at 09:39 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA code to populate data related to drop down list

    I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

    Here's some sample sheets showing what I think you're looking to do.
    INDEX/MATCH Examples

    INDEX/MATCH explanation
    http://www.excelforum.com/excel-gene...ame-row.html#3
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-21-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA code to populate data related to drop down list

    Quote Originally Posted by JBeaucaire View Post
    I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

    Here's some sample sheets showing what I think you're looking to do.
    INDEX/MATCH Examples

    INDEX/MATCH explanation
    http://www.excelforum.com/excel-gene...ame-row.html#3
    Thanks for your reply. I am new to excel programming and finding it a little bit difficult to write the function that I need. Could you help me writing the below function:

    Cell A1(Dropdown): Yes, No
    Cell A2: Home (if A1 = Yes)
    Cell A2: Office (if A1 = No)

    The moment I select Yes or No in A1 , A2 should automatically populate Office or Home. (Home and Office are hard coded values and should be written in the function it self. I am not writing Home and Office in some other part of the excel sheet.)

    Could you please give me a basic function that I can copy in cell A2.
    Please help.

    Thanks,
    Sunil
    Last edited by ExcelGyan; 10-21-2010 at 11:39 AM.

  4. #4
    Registered User
    Join Date
    10-21-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA code to populate data related to drop down list

    Could any one please help.

    Thanks,

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA code to populate data related to drop down list

    ExcelGyan, this is the programming forum and your question is clearly a "formula" need.

    It's unclear what direction you're copying the cell A2, but based on your info in post #3:

    =IF(A1="", "", IF(A1="No", "Office", "Home"))

  6. #6
    Registered User
    Join Date
    10-21-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA code to populate data related to drop down list

    Quote Originally Posted by JBeaucaire View Post
    ExcelGyan, this is the programming forum and your question is clearly a "formula" need.

    It's unclear what direction you're copying the cell A2, but based on your info in post #3:

    =IF(A1="", "", IF(A1="No", "Office", "Home"))
    Thanks for your help. It worked for me.

    There is another similar problem I am facing.
    Now I need to set the data of 10 columns based on selection of the dropdown. The dropdown can have 5 type of values.
    To achieve this scenario I can not use above method rather I will have to write a VBA macro.
    I have written a macro but that macro gets activated based on a selection in the ComboBox Active X control that I have created. This ComboBox has got the 5 values. Based on the ComboBox selection the 10 column values gets populated and it is working fine currently. The VBA code works on the Change method of this ComboBox.

    I want to replace this comboBox Active X control with a normal Dropdown and based on the dropdown selection I want the 10 column values to be populated. How can I run the VBA code based on the dropdown selection ?

    Please help.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA code to populate data related to drop down list

    Quote Originally Posted by ExcelGyan View Post
    How can I run the VBA code based on the dropdown selection ?
    By using a worksheet_change event macro in the sheet module.

  8. #8
    Registered User
    Join Date
    10-21-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA code to populate data related to drop down list

    Quote Originally Posted by JBeaucaire View Post
    By using a worksheet_change event macro in the sheet module.
    I tried with worksheet_change but it throws error. My code is as below:

    Please Login or Register  to view this content.
    The dropdown list is at C9 cell and changes will be reflecting at D8 cell.
    I am getting error in the above code at the point when I try to assign the value in D8 cell. I get error 9 as Subscript out of range. I get error after selection of value in the dropdown in cell C9.

    Kindly suggest where am I wrong and what should I change.

    Thanks,
    Last edited by ExcelGyan; 10-26-2010 at 04:32 AM.

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA code to populate data related to drop down list

    Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to EDIT that post above and put code tags around that code you used. (Like shown in my signature and explained in the rules)

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA code to populate data related to drop down list

    I've got a solution waiting to post. Please update post #8 as requested.

  11. #11
    Registered User
    Join Date
    10-21-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: VBA code to populate data related to drop down list

    Quote Originally Posted by JBeaucaire View Post
    I've got a solution waiting to post. Please update post #8 as requested.
    Hi,

    Sorry for the mistake. I have updated the code as per the rules.

    Thanks,

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA code to populate data related to drop down list

    Worksheet_Change macros are triggered when a cell value is changed on the sheet. If the macro itself then writes a change onto the sheet, it triggers the macro again. Oops.

    So it is typical that you turn off other macros until the ws_change macro is done with its work, then turn them back on. This avoids issues.

    Also, typically ws_change macros need some code to first test that the correct cells were changed before it does anything else.

    Lastly, you need to insure the macro performs properly if a bunch of cells are changed at the same time. Most people who are only watching one cell exit the sub if that happens, I like to loop through all the cells and see if one of them was actually the watched cell.

    Having said that, here's how I would write your ws_change macro:
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    10-21-2010
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: [Solved] VBA code to populate data related to drop down list

    Thanks JB.

  14. #14
    Registered User
    Join Date
    10-03-2011
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    VBA code to populate data related to drop down list

    sorry delte my comment
    Attached Files Attached Files
    Last edited by buju03; 10-13-2011 at 11:14 AM.

  15. #15
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: VBA code to populate data related to drop down list

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

+ 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