Autofilter TextBox with Multiple ActiveX Option Buttons

[sdennant]

Hi there,

I can't seem to wrap my head around this... Basically i have create a worksheet with a textbox whereby when i search for a word it autofilters a selected column. I also have three option buttons that in theory, i would want a user to select whichever button and that would dictate where that textbox searches. I only have one text box and three different buttons.

What i found that happens is, if a user selects one button and types, it filters correctly, then if you select another one it keeps the first filter and applies a second. but i actually want it only to filter one at a time if that makes sense?

I've also attached a screenshot of the search bar textbox i have created.

Here's the code:

Private Sub NameTextBox_DropButtonClick()

If ActiveSheet.OptionButton1 = True Then
    ActiveSheet.ListObjects("Names").Range _
    .AutoFilter Field:=19, Criteria1:="=*" & NameTextBox.Text & "*"
    Else
        End If

If ActiveSheet.OptionButton2 = True Then
    ActiveSheet.ListObjects("Names").Range _
    .AutoFilter Field:=17, Criteria1:="=*" & NameTextBox.Text & "*"
    Else
        End If
If ActiveSheet.OptionButton3 = True Then
    ActiveSheet.ListObjects("Names").Range _
    .AutoFilter Field:=18, Criteria1:="=*" & NameTextBox.Text & "*"
    Else
        End If
    
End Sub

 

[CountTepes]

Before each filter, switch off autofiltering and then back on again. That will clear the previous filter.

ActiveSheet.AutoFilterMode = False
ActiveSheet.AutoFilterMode = True

 

[sdennant]

Thanks for your message CountTepes, but it throws up a few errors. Using the above code do you know where exactly I should place this new code?

 

[sdennant]

Before each filter, switch off autofiltering and then back on again. That will clear the previous filter.

ActiveSheet.AutoFilterMode = False
ActiveSheet.AutoFilterMode = True

Thanks for your message CountTepes, but it throws up a few errors. Using the above code do you know where exactly I should place this new code

 

[Domenic]

To clear the filter for a ListObject, try...

    With ActiveSheet.ListObjects("Names")
        If .ShowAutoFilter Then
            With .AutoFilter
                If .FilterMode Then .ShowAllData
            End With
        End If
    End With

However, your macro can be re-written as follows...

Private Sub NameTextBox_DropButtonClick()

    Dim objListObject As ListObject
    Set objListObject = ActiveSheet.ListObjects("Names")

    With objListObject
        If .ShowAutoFilter Then
            With .AutoFilter
                If .FilterMode Then .ShowAllData
            End With
        End If
    End With
  
    Dim fieldNumber As Long
    If ActiveSheet.OptionButton1 Then
        fieldNumber = 19
    ElseIf ActiveSheet.OptionButton2 Then
        fieldNumber = 17
    ElseIf ActiveSheet.OptionButton3 Then
        fieldNumber = 18
    End If
  
    objListObject.Range.AutoFilter Field:=fieldNumber, Criteria1:="=*" & NameTextBox.Text & "*"
        
End Sub

Hope this helps!

 

[sdennant]

To clear the filter for a ListObject, try...

    With ActiveSheet.ListObjects("Names")
        If .ShowAutoFilter Then
            With .AutoFilter
                If .FilterMode Then .ShowAllData
            End With
        End If
    End With

However, your macro can be re-written as follows...

Private Sub NameTextBox_DropButtonClick()

    Dim objListObject As ListObject
    Set objListObject = ActiveSheet.ListObjects("Names")

    With objListObject
        If .ShowAutoFilter Then
            With .AutoFilter
                If .FilterMode Then .ShowAllData
            End With
        End If
    End With
 
    Dim fieldNumber As Long
    If ActiveSheet.OptionButton1 Then
        fieldNumber = 19
    ElseIf ActiveSheet.OptionButton2 Then
        fieldNumber = 17
    ElseIf ActiveSheet.OptionButton3 Then
        fieldNumber = 18
    End If
 
    objListObject.Range.AutoFilter Field:=fieldNumber, Criteria1:="=*" & NameTextBox.Text & "*"
       
End Sub

Hope this helps!

You're an absolute genius mate, thank you. This forum is amazing.

I have one more request, is it possible that after each search result it goes back to the top of the page? As of now, it filters perfectly across all buttons/columns but once i search a second it depending on how large the first result is to the second, it looks like it's blank cells but really we just need to scroll to the top.

I'm trying to make this as user friendly as possible for people that dont know how to use excel that much.

Also, one more thing and this is okay if not but i have another code attached to the search button which essentially calls the NameTextBox_DropButtonClick() but is there a way to search without that button and hit enter/return? If i didnt include that button, the dropbuttonclick was called by hitting F4.

Wishing you safety during this time.

best,
Steven

 

[Domenic]

To scroll to the top, try...

activesheet.range("A1").select

 

[sdennant]

To scroll to the top, try...

activesheet.range("A1").select

Genius again mate. thank you.

any luck with the enter/return key? I know i'm asking a lot.

 

[Domenic]

How about designating your search button as the default command button? This way the user can either click on the button or press ENTER to trigger the command button Click event (assuming that you have no other command buttons or, if you do, no other command button has focus).

If this will work for you, first move your code to the command button's Click event...

Private Sub CommandButton1_Click() 'change the name accordingly
    'your code here
    '
    '
End Sub

Then, simply set the Default property for the command button to True.

 

[Domenic]

Of course, one can always Tab over to the search button, and then press ENTER.