Excel Dynamic Search across 2 columns

[unkn0wn995]

Hi Everyone,

New to this forum, as well as understanding VBA in excel,


Basically, I've got a tooling spreadsheet, and I've created a dynamic search box , where based on operators input they can search for item they need (it only searches column A currently), it gets filtered with help of VBA code.
But now I've been requested to change it, so operators searches as column A and B (so if they type either part name or part number it will search by either of columns and show results (as operator might go by name of whatever is in column A or by part code/name in Column B)
My only issue is I can't seems to figure out how to change my search box / VBA code to make sure it search and filters out by either column A/B.

I've included a picture of spreadsheet as well as VBA code, so one of you excel geniuses can help me with this.

 

[Georgiboy]

There are other options, like the last part of post 5 for example, however, I just think the two text boxes would be a simple solution. You could also have one search box with a dropdown box to specify which column you wish to search.

 

[unkn0wn995]

For example if you add another textbox named: Textbox2 to search the second column, you could try something like the below:

Private Sub TextBox1_Change()
    Dim lo As ListObject, str As String
   
    str = "*" & Me.TextBox1.Text & "*"
    Set lo = Sheet1.ListObjects("Data")
   
    With Application
        If str = "**" Then
            lo.AutoFilter.ShowAllData
        ElseIf .CountIf(lo.ListColumns("Tool Description").DataBodyRange, str) > 0 Then
            lo.Range.AutoFilter 1, str, xlFilterValues
        Else
            lo.AutoFilter.ShowAllData
        End If
    End With
End Sub
Private Sub TextBox1_Enter()
    Me.TextBox2 = ""
End Sub

Private Sub TextBox2_Change()
    Dim lo As ListObject, str As String
   
    str = "*" & Me.TextBox2.Text & "*"
    Set lo = Sheet1.ListObjects("Data")
   
    With Application
        If str = "**" Then
            lo.AutoFilter.ShowAllData
        ElseIf .CountIf(lo.ListColumns("Part Code").DataBodyRange, str) > 0 Then
            lo.Range.AutoFilter 2, str, xlFilterValues
        Else
            lo.AutoFilter.ShowAllData
        End If
    End With
End Sub
Private Sub TextBox2_Enter()
    Me.TextBox1 = ""
End Sub

I've given this a try, but it only works till i protect sheet, as i don't want anybody else to edit anything, and only access search box and selected cell.
What's the best way to go about using this on protected sheet?

 

[Georgiboy]

You can add the below code to the ThisWorkbook module:

Private Sub Workbook_Open()
    Sheet1.Protect "password", , , , 1
End Sub

It will protect the worksheet but allow VBA to make changes.

The issue with it is, if you unprotect the sheet to make changes, you will need to close and re-open the workbook to protect it in the same way again.

 

[unkn0wn995]

You can add the below code to the ThisWorkbook module:

Private Sub Workbook_Open()
    Sheet1.Protect "password", , , , 1
End Sub

It will protect the worksheet but allow VBA to make changes.

The issue with it is, if you unprotect the sheet to make changes, you will need to close and re-open the workbook to protect it in the same way again.

I've added the code into Workbook module but keep getting this error, either for search box 1 or 2, I Is there something i am doing wrong?

 

[Georgiboy]

The part below as i displayed is very important in making this work:

, , , , 1

It needs to be:

Sheet1.Protect "RI", , , , 1

You also need to make sure it is sheet1 that the table resides in.

 

[unkn0wn995]

The part below as i displayed is very important in making this work:

, , , , 1

It needs to be:

Sheet1.Protect "RI", , , , 1

You also need to make sure it is sheet1 that the table resides in.

I've added the missing line off code, but still getting the same error code on both lines?

 

[Georgiboy]

Have you saved, closed the workbook and then re-opened?

The code fires when the workbook is opened.

 

[unkn0wn995]

Have you saved, closed the workbook and then re-opened?

The code fires when the workbook is opened.

I have now and it works beautifully well.

Thank you very much for you help!

Much appreciated!

 

[Georgiboy]

You're welcome, thanks for the feedback.

Remember that if you unprotect the sheet manually and re-protect manually, you will get the error again. Closing and reopening the workbook will solve the error.