Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ComboBox with dynamic list (2000)

    As a follow up to Steve's <post#=425469>post 425469</post#>
    I have put together the attached worksheet with dynamic lists and a combobox.

    To make it work click on the buttons 1 thru 5, then try step 6.

    The cbo works fine until new items are added to the original dynamic list. How can I change the ListRows property or
    make the cbo expand to show the new items?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ComboBox with dynamic list (2000)

    Add the following code to the code module of the DYNAMIC worksheet (right click the sheet tab and select View Code to open the module):

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Me.ComboBox1.ListFillRange = "SortedList"
    End If
    End Sub

    This event procedure updates the ListFillRange each time the user changes something in column A.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ComboBox with dynamic list (2000)

    I was assuming you were either using a cbo from the FORMS toolbar or datavalidation which updates automatically.

    If you are going to use the CONTROLS TOOLBOX items with code, you could just as well skip all that named ranges and formula stuff (I did this to avoid the use of any coding, you could use the code to extract the items and sort them.

    John Walkenbach has a "tip" to extract the unique items in a list to fill a listbox, which could be adapted. This uses the "AddItem" instead of the listfillrange. You don't need a fillrange if you are going to use code anyway.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox with dynamic list (2000)

    Steve and Hans,

    Thanks for the tips. I'll try them out and get back if I need more help.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox with dynamic list (2000)

    J Walk's NoDupes routine looks like the most direct method. but I'm having a small problem.
    I changed the following code from a Listbox (J Walk) to a combobox (mine).

    <pre>' Add the sorted, non-duplicated items to a ListBox '''' Combobox ''''
    For Each Item In NoDupes
    ActiveSheet.OLEObjects("ComboBox1").AddItem Item
    ' UserForm1.ListBox1.AddItem Item
    Next Item</pre>



    Problem: oleobjects don't support the AddItem method.
    I seached the class for an alternate but wasn't able to find what I wanted. Maybe I missed it.

    Should I use something like:

    ActiveSheet.OLEObjects("ComboBox1").ListFillRange = Collection

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ComboBox with dynamic list (2000)

    You just have to use:
    <pre>ComboBox1.AddItem Item</pre>


    The code should be in the worksheet object that contains the combobox.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox with dynamic list (2000)

    Steve
    Can you be more specific.
    Basically, the question boils down to:
    How do I populate a CONTROL TOOLBOX combobox with items
    from a dynamic range which contains blanks? I don't want the
    blanks to show in the cbo and I need the cbo to expand or
    contract as the dynamic range changes.

    I have tried combining Hans' and your suggestions as follows but I'm
    still not getting what I want. This code is in the worksheet object.
    <pre>Option Explicit

    Private Sub ComboBox1_Change()
    ''Some code here

    'I don't want to be adding an item everytime I make
    'a selection from the cbo

    End Sub



    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Item

    If Not Intersect(Target, Range("A:A")) Is Nothing Then

    Me.ComboBox1.AddItem Item
    'Error: Permission denied

    '''''''''''''''''''''''''''''''''''''
    ' Other things I've tried:
    ' Me.ComboBox1.AddItem Target
    'Error: Permission denied


    '''''''''''''''''''''''''''''''''''''''''''''''''' '''
    ' Me.ComboBox1.ListFillRange = "SortedList"
    'This works if I keep the sorted list going
    'but I'm trying to eliminate that step.
    'and just fill the cbo with items from
    'the UnsortedList which contains blanks
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''

    End If
    End Sub</pre>



    Thanks for your patience

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox with dynamic list (2000)

    Likewise, the cbo needs to contract dynamically as items are deleted from the UnsortedList

    All of this works if I keep the Worksheet_change event to update
    the ListFillRange to the SORTEDList as Hans has suggested.

    What I was hoping to do is eliminate the SORTED LIST

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ComboBox with dynamic list (2000)

    What about something like this (I assume the list is unique and did not extract out a unique list, but that could be added if desired)

    Steve
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("a:a")) Is Nothing Then
    Dim vRange
    Dim vList()
    Dim iCount As Integer
    Dim i As Integer
    Dim j As Integer
    Dim vTemp

    ComboBox1.Clear
    vRange = Range(Range("A1"), Range("A65536").End(xlUp)).Value
    If IsEmpty(vRange) Then Exit Sub 'Range is empty
    If VarType(vRange) < vbArray Then
    ComboBox1.AddItem vRange 'range has only 1 item
    Exit Sub
    End If
    j = UBound(vRange)
    ReDim vList(1 To j)
    iCount = 0
    'eliminate blanks or just spaces
    For i = 1 To j
    If Trim(vRange(i, 1)) <> "" Then
    iCount = iCount + 1
    vList(iCount) = vRange(i, 1)
    End If
    Next
    If iCount = 0 Then Exit Sub 'All 'blanks'
    ReDim Preserve vList(1 To iCount)
    'sort the list
    For i = 1 To iCount - 1
    For j = i + 1 To iCount
    If vList(i) > vList(j) Then
    vTemp = vList(j)
    vList(j) = vList(i)
    vList(i) = vTemp
    End If
    Next j
    Next i
    'add sorted list to combobox
    For i = 1 To iCount
    ComboBox1.AddItem vList(i)
    Next
    End If
    End Sub</pre>


  10. #10
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox with dynamic list (2000)

    Steve
    Have you tried this code with a CONTROL TOOLBOX cbo?

    It's hanging on the first line for me (Combobox1.Clear)

    <pre>When I checked the VBE list of methods and properties of an OLE OBJECT, I could not find
    Clear or
    AddItem, which you use at the end of your procedure.

    Perhaps this works on a FORMS cbo</pre>



    This may just be easier to stick with the intermediate SortedList
    and use the ListFillRange property ???

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ComboBox with dynamic list (2000)

    Yes that is the combobox I tested it on.

    Where is the code? I placed the code in the sheetobject containing the combobox and the list. (my test had them together)

    If the list and the combobox are on different sheets, then the "copmbobox" should be referenced with its sheet name, something:

    Sheet1.combobox1.clear

    Sheet1.combobox1.additem item

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox with dynamic list (2000)

    Let column A from A2 on house the raw list on the sheet named DYNAMIC...

    {"UnsortedList";"Baseball";"Football";"Volleyball" ;0;"Soccer";"Chess";0;"Hockey";0;0;0;"Basketball"; "Polo"}

    where 0's stand for empty cells.

    In B1 enter:

    =MATCH(REPT("z",255),A:A)

    In B2 enter: Sort

    In B3 enter & copy down:

    =IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0)),SUMPRODUCT( (A3>OFFSET($A$3,0,0,$B$1,1))+0)+1,"")

    The ISNA(...) bit is in order to avoid duplicates of an item.

    In C2 enter: Rank

    In C3 enter & copy down:

    =IF(N(B3),RANK(B3,$B$3:INDEX(B:B,$B$1),1)+COUNTIF( $B$3:B3,B3)-1,"")

    In D1 enter:

    =MAX(C3:INDEX(C:C,B1))

    In D2 enter: SortedList

    In D3 enter & copy down:

    =IF(ROW()-ROW(D$3)+1<=$D$1,INDEX(A:A,MATCH(ROW()-ROW(D$3)+1,C:C,0)),"")

    Now you have everything you need to assign a name to the range in column D...

    Activate Insert|Name|Define.
    Enter SortedList as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =DYNAMIC!$D$3:INDEX(DYNAMIC!$D:$D,MATCH("*",DYNAMI C!$D:$D,-1))

    Set up a combo box (e.g., from Control Toolbox) with ListFillRange set to: SortedList and LinkedCell to some cell of your choice.

    The set up will adjust itself whenever you add any new non-duplicate item to the raw list.

    If you are on Excel 2003, there will be no need for copying down formulas when the raw list expands or contracts after some small adjustments to the set up described above.
    Microsoft MVP - Excel

  13. #13
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox with dynamic list (2000)

    Aladin
    Your method seems to add 3-4 intermediate steps in col B-D. My original method
    has only one intermediate step (SortedList) which I am attempting to get rid of and
    work only with a single raw list containing blanks yet with a conbo box that will reveal
    no blanks and will expand and contract dynamically.

    In the attached file which uses Hans' suggestion of :
    <pre>
    Me.ComboBox1.ListFillRange = "SortedList"

    </pre>



    in the Worksheet_Change event, the cbo works as I want it to, as described above.

    The only change I would make to this is possibly eliminating
    the SortedList and using just an UnsortedList as Steve has indicated
    is possible. As yet I can't get Steve's code to work for me. Hence:


    Steve
    I am attaching a file with your code in the Worksheet_Change event
    as an option for testing.
    I still can't get it to work on my machine (xp with xl2000).
    It hangs on the line: ComboBox1.Clear
    Can you make any changes needed and re-post it?
    Thanks

    BTW I found another problem -- the formula in Step 3 is leaving
    the first item out and needs a minor tweak. Can you adjust this for
    me?
    Also please explain the portion: COUNTIF(UnsortedList,""<"" &UnsortedList)

    Thanks

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ComboBox with dynamic list (2000)

    Concerning the "sorted List formula"
    You need to modify the formula, since you are starting in row2 instead of row1. You need to use row()-2 not row()-1. Also I found a flaw in the formula logic. The first cell must have a value or it will put a zero instead of the frist value. To fix this use this formula in B2.

    =INDEX(UnsortedList,MATCH(ROW()-2,COUNTIF(UnsortedList,"<" &UnsortedList)-ISBLANK(UnsortedList),0))

    Thinking about the formula, a shorter formula would be:
    =INDEX(UnsortedList,MATCH(ROW()-1,COUNTIF(UnsortedList,"<=" &UnsortedList),0))

    This does not require the row-2 since instead of having zero for the first value (where no values are less than it), it uses <= so that the first value in the list is 1 and blanks will remain as 0. Now it matches from 1-end, instead of 0 to end.

    Then Edit (<F2>), select B2:B25, confirm with ctrl-shift-enter.

    Also your definition of "sortedList" should refer to:
    =OFFSET(DYNAMIC!$B$2,0,0,COUNTA(UnsortedList),1)

    Concerning the combobox and the code. You can not clear, or additems to a combobox if it has a listfillrange filled in. You must clear this property in object. Also since you have some code which keeps changing this, this must also be changed. If you want you could just use the line:

    ComboBox1.ListFillRange = ""
    Right before the:
    ComboBox1.clear
    Line to clear the listfillrange.

    I noticed 1 other thing while I was "playing". The sort sorts by ASCII, so all the Uppercase comes befor any lower case, so if you have a word starting with a lower case "a" it will be after an upper case "Z" and not with the "A"s. If this is an issue (and it would be to me), change the line of code to:
    Add UCASE to the comparison of the values in the sort sequence:

    If UCase(vList(i)) > UCase(vList(j)) Then

    Hope this helps,
    Steve

  15. #15
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox with dynamic list (2000)

    Thanks Steve

    With one very minor change (starting the vRange at A2 instead of A1 - to allow for the header), it's working perfectly.

Page 1 of 2 12 LastLast

Posting Permissions

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