Announcement

Collapse
No announcement yet.

Use InputBox to return AutoFilter Criteria1

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

  • Use InputBox to return AutoFilter Criteria1



    Hello, I'm new to the forum and could use some assistance. I'm trying to create a macro to filter data in a range based on user input. I'm not very familiar with InputBox and can't seem to get it to work. The user enters in the producer name and then that should define the criteria1 in the AutoFilter. Below is the section of my macro that I am having problems with. Any help or suggestions would be greatly appreciated.

    Code:
    Selection.AutoFilter
        Dim strName As String
    
    
        strName = InputBox(Prompt:="Select Producer", _
              Title:="Enter Producer", Default:="Producer Name Here")
    
    
         If strName = "Producer Name here" Or _
               strName = vbNullString Then
    
    
               Exit Sub
    
    
            Else
            
            ActiveSheet.Range("$A$1:$J$69").AutoFilter Field:=8, Criteria1:= _
            "=strName", Operator:=xlAnd
             
            End If
    Thank you!

  • #2
    Re: Use InputBox to return AutoFilter Criteria1

    No speech marks required around strName

    Code:
    ActiveSheet.Range("$A$1:$J$69").AutoFilter Field:=8, Criteria1:= _ 
        "=" & strName, Operator:=xlAnd
    Last edited by cytop; April 15th, 2013, 15:33. Reason: Corrected code tags
    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: Use InputBox to return AutoFilter Criteria1

      Thank you Roy! That worked. I knew it had to be something simple, I just couldn't see it.

      Comment


      • #4


        Re: Use InputBox to return AutoFilter Criteria1

        I would actually use to allow for changes in the size of the data

        Code:
        ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=8, Criteria1:= _ 
        "=" & strName, Operator:=xlAnd
        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

        Working...
        X