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

    combobox error (2000)

    I have set up a database on one sheet (Db) and an extraction sheet with Criteria cells for a search of the db.
    In the Criteria range I have used data validation on the first cell ("Status" - in the attached wbk) to limit data entry and provide a drop-down box.
    This works fine as the validation list has only 4 items.

    The 2nd Criteria cell (Unit) has 32 possible items. Since the "Data Validation drop-down" box is limited to viewing only 8 items at a time, I have tried instead using a "control toolbox" combobox with the Change Event triggering the extraction. Unfortunately, this event is causing it to hang. (After using it, the ShowAll procedure crashes and I have to reset the Application.EnableEvents. It seems that the combobox change event is not completing, which should re-enable events.)

    Any help?

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

    Re: combobox error (2000)

    The ShowAll procedure clears (among others) the linked cell of the combo box. This triggers the On Change event of the combo box, which fails. You can remedy this by inserting

    On Error Resume Next

    at the beginning of the ComboBox1_Change procedure.

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

    Re: combobox error (2000)

    Hans
    I've cleaned up a few things and added the On Error.... (See new attachment)

    I'm still getting a "latent image" on the spreadsheet when I use the combo box. This "image" disappears if I scroll, but that shouldn't be necessary.
    What is the cause of the image? and how can we keep it from appearing?

    thanks

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

    Re: combobox error (2000)

    I'm sorry, what do you mean by a "latent image"? The workbook you attached seems to work OK on my PC.

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

    Re: combobox error (2000)

    I found by stepping thru the code that the problems started at the advanced filter code. I was able to stop the problems by first activating
    the sheet which holds the list I wanted filtered. Evidently xl doesn't like filtering while another sheet is active:

    <pre> Sheets("Db").Activate
    [TenantList].CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Tenants").Range("Criteria") , _
    CopyToRange:=Sheets("Tenants").Range("Extract")
    Sheets("Tenants").Activate </pre>


  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combobox error (2000)

    You can eliminate some screen flashing by putting:

    <pre> Application.ScreenUpdating = False
    </pre>


    at the beginning of that procedure and:

    <pre> Application.ScreenUpdating = False
    </pre>


    at the end.
    Legare Coleman

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

    Re: combobox error (2000)

    Thanks Legare
    I forgot about screen updating

Posting Permissions

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