Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Toronto, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo box applied filters won't work!

    Hello all,

    My story goes like this...
    I have created two macros which work fine individually. The macro named FILTER references a cell and applies a filter based on the cell to another range of cells. The second macro, slyly named UNFILTER, removes the filter that was applied by the FILTER macro. Both seem to work fine when run from command buttons.
    Now, I would like to call the FILTER macro from the 'Change' event of a combo box. When I do this I get a run-time error '1004', which stops the module on the CriteriaRange line of code.

    Can anyone tell me how I am messing this up.

    The code I am using is as follows.

    MACRO #1
    Sub FILTER()
    If Worksheets("PRODUCTION").Range("b3") = "All Units" Then
    UNFILTER
    Else


    Worksheets("PRODUCTION").Range("b5:b5000").Advance dFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Worksheets("PRODUCTION").Range("b2:b3"), Unique:=False
    End If

    End Sub

    MACRO #2
    Sub UNFILTER()
    If Worksheets("PRODUCTION").Range("b3") <> "All Units" Then
    ActiveSheet.ShowAllData
    Else
    End If

    Call from COMBOBOX
    Private Sub ComboBox2_Change()
    Call FILTER

    End Sub

    Thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Combo box applied filters won't work!

    Hi,
    There doesn't appear to be anything wrong with your code as it stands (Just to check, I copied it into a workbook and it worked exactly as expected.) Does it always fail when you try to use it or only under certain circumstances? Is your workbook small enough (<100k) to post here so we can take a look at it?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Apr 2001
    Location
    Toronto, Ontario, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box applied filters won't work!

    Hi Rory,

    Thanks, for your response.

    I have attached a sample of the file. What I would like is for the action of the combobox to mimic the action of the two command buttons.

    Let me know if you see anything amiss.

    Rob Miller.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Combo box applied filters won't work!

    Hi Rob,
    I took a look at your spreadsheet and I have to say it worked exactly as expected! The only thing wrong with it is that the Unfilter macro only runs if the Criterion is not "All Units" rather than if it is.
    Do you get the runtime error every time you try and use the combobox?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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