Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combobox problems (2000)

    I have some code which is designed to fill and show up to 3 comboboxes on a userform. The entries are derived directly from data on the "Categories" worksheet (attachment).

    Combo1 contains the unique values in column A. When the user selects a value in Combo1, the code iterates the spreadsheet rows with the appropriate values in column A, and if there are non-null column B values, they are added to Combo2 and the control becomes visible.

    The same method is repeated to derive Combo3 entries from the Combo2 selection, and show the control if there are any entries. The test for a non-empty combobox is:

    If ComboBox2.ListCount > 0 Then ComboBox2.Visible = True

    This works for ComboBox2, but not ComboBox3. I have established that it fails when there are non-empty cells in column B, above the range of current interest. For instance, referring to the attached sheet, the method will fail for:

    Office Stationery -> Desktop & Personal

    because the row above this contains the value "Ruled" in column C:

    Office Stationery -> Books -> Ruled

    The behaviour is consistent for such cases, and Combo3 will end up containing a single null entry and has a ListCount of 1 - it should be zero.

    I can fudge a fix for the specific data I have by making the condition:

    If ComboBox3.ListCount > 1 Then ComboBox3.Visible = True

    but this is, of course, not generally satisfactory.

    I'm hoping that somebody can see what's happening here, and what the fix is of course <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. I've been brickwalling with this for hours, and I'm out of ammo I'm afraid.

    thanks

    Alan

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

    Re: Combobox problems (2000)

    Try replacing<pre>If cellAnew = combo2 And cellBnew <> cellB Then</pre>

    in ComboBox2_Change by<pre>If cellAnew = combo2 And cellBnew <> cellB And cellBnew <> "" Then</pre>

    This prevents empty entries from being added to the list.

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

    Re: Combobox problems (2000)

    Additional remark: a cell is never "Null". You should compare it with the empty string "" to see if it is not empty. In your example it doesn't matter, since C1 is empty, but if C1 contains a value, it will be skipped if you compare with Null.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox problems (2000)

    A million thanks Hans. I just couldn't spot it for dear life. My own necktop logic failed to alert me that an empty string might still be a legitimate list entry addition.

    And yes, the "null" was inappropriate in this context. I started out with empty strings as you point out, then started using Null in desperation for a fix. Must have left one or two in there <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Still, I'll look up the nitty gritty on the use of Null in VB - I don't think I've ever dug in that deep.

    cheers

    Alan

Posting Permissions

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