Announcement

Collapse
No announcement yet.

Turn Off AutoFilter Mode

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Turn Off AutoFilter Mode



    hi

    i have a userform that uses auto filter the filter the search results. I am wondering if someone can show me how to turn the auto-filter off once action (printing) is completed.

    below is the code I am using:

    Code:
    Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Set ws = Sheets("ComplaintData")
    
    With ws
                   .AutoFilterMode = False
                    With .Range("A1:L1")
                         .AutoFilter
                         If ComboBox1.Value = "Month" Then
                         .AutoFilter Field:=11, Criteria1:=ComboBox2
                         
                         ElseIf ComboBox1.Value = "Category" Then
                         .AutoFilter Field:=7, Criteria1:=ComboBox2
                         
                         ElseIf ComboBox1.Value = "Customer" Then
                         .AutoFilter Field:=3, Criteria1:=ComboBox2
                         
                         ElseIf ComboBox1.Value = "Owner" Then
                         .AutoFilter Field:=9, Criteria1:=ComboBox2
                         End If
                    End With
                    
                    With ws.PageSetup
            .PrintTitleRows = "$1:$1"
            .PrintTitleColumns = ""
      End With
        
       
        With ws.PageSetup
            .CenterHorizontally = True
            .Orientation = xlLandscape
            .Draft = False
            .FitToPagesTall = False
            .FitToPagesWide = 1
           
        End With
         
        With Application
            .Dialogs(xlDialogPrinterSetup).Show
            ws.PrintOut
            ws.Visible = True
            .ScreenUpdating = True
        End With
     End With
     
     Unload Me
    End With
    
    End Sub
    Also, I was given a code in this forum to handle the "Item not found" situation. Where would I insert it in this code. If the searched item doesnt exist, then I would like the message box to come up before it shows the printer selection box and if the searched item exists then directly show the printer selection box.....following were the two lines

    Code:
    On Error GoTo ErrFindClick
    Code:
    ErrFindClick:
        MsgBox " " & combobox1.Value & " Not Found!"
        Exit Sub
    cheers

  • #2
    Re: autofilter - turn off

    have you looked at the similar items listed at the top of your Thread. Item 2
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: autofilter - turn off

      Code:
      ws.ShowAllData

      Comment


      • #4


        Re: autofilter - turn off

        vikrampnz - Do a quick search of OzGrid under my name and key words
        AutoFilter or Filter or Advanced Filter. I have posted lot of codes covering all sorts for this

        jiuk

        Comment

        Working...
        X