+ Reply to Thread
Results 1 to 6 of 6

Userform Dependant Combobox

  1. #1
    Registered User
    Join Date
    06-20-2011
    Location
    London England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Userform Dependant Combobox

    http://www.teachexcel.com/excel-help/ev.php?i=121128

    Further to the question and answers in the thread, link above, regarding Userform Dependant Combobox.
    Excellent answer, it does exactly as requested. I am new to forums and am trying to expand my knowledge.

    I am trying to do the same sort of thing but with dynamic ranges, variable number of options in each combo box and a group of 4 combo boxes. I.e. one result cascades to the next combo box making sure that an inappropriate result cannot be entered on the userform.

    Before trying to work out the code I am working on the excel dynamic ranges using ‘Match’ from the first option to provide the offset reference point for the second ‘Match’.

    '=OFFSET((CELL("address",INDIRECT(CONCATENATE("R",ROW($KJ$31)+((MATCH($JQ$25,$KI$32:$KI$131,0))),"c",COLUMN($KJ$31)),FALSE))),MATCH($KJ$26,$KJ$32:$KJ$131,0),4,COUNTIF($KJ$32:$KJ$131,$KJ$26),1)


    This may be a too complex solution. Perhaps nested 'Match' does not work in excel.
    If this does not work I can go back to creating each option list separately, which may be simpler.

    I can get four option boxes working sequentially in the excel workbook. However, I am trying to move all user interfaces to userforms.

    I know there are effectively two questions here but I am only looking for one solution, which is how to make multiple Userform Dependant Comboboxs of variable length.

    All help welcome.
    Last edited by bojum01; 06-27-2011 at 07:54 PM. Reason: Marked Solved

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Userform Dependant Combobox

    Hi bojum01

    Attached is a sample of Dependent ComboBoxes on a UserForm (only two in the example...it can be expanded with defined dependencies) using Dynamic Named Ranges.

    This sample has been extrapolated from this Thread which is based on Worksheet Comboboxeshttp://www.excelforum.com/excel-gene...down-list.html
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    06-20-2011
    Location
    London England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Userform Dependant Combobox

    Thanks, I will work my way through the code and sample.
    I have sorted the on worksheet formula. It is below for info.
    '=OFFSET((INDIRECT(CONCATENATE("R",ROW(Data_Array_A_S)-1+MATCH(cbo_B18_Select,Data_Array_A_Valid_B18,0)-1+MATCH(cbo_B19_Select,Data_Array_A_Valid_B19,0),"C",COLUMN(Data_Array_A_S)+1+1),FALSE)),MATCH(cbo_B23_Select,Data_Array_A_Valid_B23,0),1,COUNTIF(Data_Array_A_Valid_B23,cbo_B23_Select),1)

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform Dependant Combobox

    If you are using Dynamic Named Ranges I would suggest that names used correspond to the category. This simplifies the code & the controls can be populatedusing the List Property based on the selection in the first control.

    In the example I have used ComboBoxes & ListBoxes, you can of course use a ComboBox to select from & populate a ListBox based on the selection.
    Attached Files Attached Files
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Registered User
    Join Date
    06-20-2011
    Location
    London England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Smile Re: Userform Dependant Combobox

    Thank you Jaslake and royUK. A combination of both codes and samples resolved my problem.
    I had to add a little extra code to send the result to the workbook but this was just because I wanted to keep it all variables and not restrict the first level entry.
    Having multiple levels of combo box makes it more complicated as each level is formulated on all the preceding selections.
    I have noticed that it is not error tolerant but that is another level of sophistication that I will put in another thread when I am ready to go it that direction. It is good to get it working, and as long as all the base data for the lists is completed correctly, it should not give the error message. Eg. If all of the four levels are not available in a certain combination.
    Thanks again.

    bojum01

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Userform Dependant Combobox

    Hi bojum01

    Glad to be of help.

    If that satisfies your immediate need, please mark your thread as "Solved".

    To mark your thread solved do the following:
    - Go to your first post on the thread
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solved
    - Click Save

+ 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