Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ComboBox Selection (XP;SP2)

    I have a combobox's ListFillRange assigned with a rangename of cells (yellow cells). Is it possible to only display those cells in the rangename that are not blank?

    As you can see there are blank cells in the yellow range.

    Thanks,
    John

  2. #2
    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 Selection (XP;SP2)

    If the blanks can be anywhere in the range the best way is:
    1)You can (instead of using the listfillrange property) loop thru the range via a macro, check each item in the range and then ADD it to the list
    somehting like this (change range name and combobox1 name as appropriate)

    <pre>Option Explicit
    Private Sub ComboBox1_GotFocus()
    Dim rcell As Range
    For Each rcell In Range("rng")
    If rcell.Value <> "" Then _
    ComboBox1.AddItem rcell.Value
    Next
    End Sub</pre>


    If the blanks are ONLY at the end, you can

    2) redefine the range name to include ONLY cells A1:A4. You must manually change it when the number of items change

    3) Use a DYNAMIC range name:
    create a name called eg:
    rngCount
    that refers to:
    =COUNTA(Sheet1!$A:$A)
    Then define the named range for the fill:
    =OFFSET(Sheet1!$A$1,0,0,rngCount,1)

    Now whenever you add values into col A the range will expand automatically.

    BOTH of the last methods still require a macro to reset the fill range. Excel does NOT usually recheck the size of the range (only the values within them)

    <pre>Private Sub ComboBox1_GotFocus()
    ComboBox1.ListFillRange = Range("rng").Address
    End Sub</pre>


    Method is is the most general and can be used to filter out items and even to put them in alphabetical order (even if the list is not).

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Selection (XP;SP2)

    Steve,

    I tried option 1 and painted myself into a corner. I now have multiple items in my listbox and do not know how to clear them out. I added one additional item in the range and now have multiple items listed.

    I have attached my sample file.

    John

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

    Re: ComboBox Selection (XP;SP2)

    Just add the line

    ComboBox1.Clear

    at the beginning of the ComboBox1_GotFocus procedure, above the For Each rCell ... loop. This will erase the existing list items before adding new ones.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ComboBox Selection (XP;SP2)

    Hans,

    That was too easy.

    Thanks,
    John

Posting Permissions

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