+ Reply to Thread
Results 1 to 9 of 9

Combox Box : Populating values dynamically

  1. #1
    Registered User
    Join Date
    06-11-2010
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    10

    Combox Box : Populating values dynamically

    Hi,

    I am new to MS Excel.

    I have a requirement to display a list of values in a cell say B3 in Sheet1. Based on the value the user selects among the list, I have to populate the data available between a particular range from Sheet2.

    For example, if the item1 is selected in B3 of Sheet1, I have to populate the values available between C3 and H3 from Sheet2 into C3:H3 in Sheet1. If the item2 is selected in B3 of Sheet1, I have to populate the values available in C4:H4 and similiary for item3 and item4.

    Please help me in implementing this.

    Thanks & Regards
    Yuvi

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Combox Box : Populating values dynamically

    Hello Yuvi,

    Welcome to the Forum!

    This should help get you started. I have attached a workbook that has three different drop downs: Data Validation List, Forms Drop Down control, and a Control Toolbox ComboBox. Each one uses data from the same named range. This range is on "Sheet1" cells "K2:K6".

    The macros are located in two different parts of the workbook. The Forms Drop Down macro is in Module1. The code for both the ComboBox and the Data Validation List are in Worksheet events module for "Sheet1". To view the code, right click the tab for "Sheet1" and select "View Code".
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    06-11-2010
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Combox Box : Populating values dynamically

    Thanks for the solution, Leith.

    I would need your help in understanding what is Range("List_Data") within Worksheet_Change for the Data Validation List.

    I have attached the worksheet I am working on. I have added my requirement as comment in C3 of Sheet1.

    It would be great, if you can help me.

    Thanks in anticipation.

    Thanks & Regards
    Yuvi
    Attached Files Attached Files

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Combox Box : Populating values dynamically

    Hello Yuvi,

    In this version of the macro, the named range is "Options". This refers to "Sheet2" cells A4:A6. When the user selects an option from the Vlidation Drop Down on "Sheet1" cell C3, the corresponding data from "Sheet2" is copied over. The attached workbook has the macro added.

    Sheet1 Worksheet_Change Event
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-11-2010
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Combox Box : Populating values dynamically

    Leith,

    It worked well. Thanks.

    I now face another problem. I have the options from the same name and source populated into 2 cells of sheet1 - C5 and S5. Based on the user's selection in C5, I have populated the values for Item2 till item16. However in S5 I have the options again and based on this selection, I have to populate value for between Item17 and Item27 from the sheet2.

    How do I identify which cell (cell C5 or S5) is activated in the Worksheet_Change event? Also, I would want the user to edit the cells between D5 and R5 and between T5 and AD5, once the data is filled in based on the options selected.

    Request you to help me in figuring this out.



    Thanks & Regards
    Yuvi
    Attached Files Attached Files
    Last edited by Yuvarani; 06-12-2010 at 12:23 PM.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Combox Box : Populating values dynamically

    Hello Yuvi,

    The macro below has been revised to copy the selected option on the same row as the drop down. The column the drop down is in determines which columns from the source sheet are copied over. The macro has been added to the attached workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files

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

    Re: Combox Box : Populating values dynamically

    That worked perfectly.
    Thank you, Leith Ross

    Thanks & Regards
    Yuvi

  8. #8
    Registered User
    Join Date
    06-11-2010
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Combox Box : Populating values dynamically

    Hi Leith,

    I had to lock some of the cells and I wrote the below query to you. Now, I figured it out. I locked those cells that has the formula. I then protected the worksheet. It worked. Thanks.

    Ignore the message below

    Hi Leith,

    Is there by any ways I can lock editing a particular cell. For example, in the last column of my sheet I have to display the sum of all the items. I have the formula on place. Can I lock this column so that the user by mistake (knowingly / unknowingly) does not edit this column?

    Thanks & Regards
    Yuvi
    Last edited by Yuvarani; 06-13-2010 at 12:30 AM.

  9. #9
    Registered User
    Join Date
    06-11-2010
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Combox Box : Populating values dynamically

    hi Leith,

    Can you help me in resolving my problem hosted in http://www.excelforum.com/excel-prog...as-values.html ?

    Regards
    Yuvi

+ 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