Oct 15 2021 10:13 AM - edited Oct 18 2021 11:19 AM
Hi All,
I have created a search-as-you-type searchable text box for excel Table "Materials" using ActiveX Textbox by adding a VBA code (i have provided the code below) to it. I have two issues that I need answers/help:
1. The ActiveX Textbox does not work as nothing appears when I type a search value. When I hit enter or when i get out of the box the search value that i had typed shows up. Note that if I have the excel on the laptop monitor it works, i.e. I can type right in the textbox and i can see it as i type
2. The VBA code I use looks into the entire table that has multiple columns. The search data is in on column called Items and therefore does not return any result.
--------Beginning of VBA code-------
Private Sub TextBox1_Change()
Dim xStr, xName As String
Dim xWS As Worksheet
Dim xRg As Range
On Error GoTo Err01
Application.ScreenUpdating = False
xName = "Materials"
xStr = TextBox1.Text
Set xWS = ActiveSheet
Set xRg = xWS.ListObjects(xName).Range
If xStr <> "" Then
xRg.AutoFilter field:=1, Criteria1:="*" & xStr & "*", Operator:=xlFilterValues
Else
xRg.AutoFilter field:=1, Operator:=xlFilterValues
End If
Err01:
Application.ScreenUpdating = True
End Sub
--------End of VBA code-------
Thank you all in advance!
Al.
Oct 15 2021 05:04 PM
Since May 2021, this feature (data validation dropdowns filter the list in response to what you type) has been available to users of Microsoft Online. Just save the file on OneDrive and access it using a web browser.
I hope it comes to desktop Excel soon.
Oct 15 2021 05:46 PM
Oct 18 2021 11:32 AM
Oct 18 2021 12:17 PM
"it doesn't instantly filter as I type"
You may have to wait a fraction of a second for the web connection to refresh, but the selections offered do update as you type. I tested that feature just now, and confirmed that it is still working as described in Microsoft's feature announcement in Spring 2021.
Were you using a web browser to access a file stored on OneDrive? The feature won't work if you use desktop Excel to open the file.
If you would like me to investigate VBA solutions to the problem, please post a sample workbook showing your code and a working dropdown. The sample workbook doesn't need to contain anything other than a sample dropdown with five or ten choices and the code that is not working the way you want.
I'd like to verify that the code is installed in the right place, name references are correct, etc. Even if those details are all correct, there may be a different event that might provide more responsiveness,.
Oct 19 2021 07:45 AM
Oct 19 2021 05:43 PM
The KeyUp event sub runs as soon as you release the key, so it should give you snappier response. I commented out the statement turning screen updating back on as VBA does this for you automatically when it returns control to the worksheet user interface. Setting it to True will therefore perform the screen update twice, taking time needlessly and causing a screen flicker.
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim xStr, xName As String
Dim xWS As Worksheet
Dim xRg As Range
On Error GoTo Err01
Application.ScreenUpdating = False
xName = "Materials"
xStr = TextBox1.Text
Set xWS = ActiveSheet
Set xRg = xWS.ListObjects(xName).Range
If xStr <> "" Then
xRg.AutoFilter field:=1, Criteria1:="*" & xStr & "*", Operator:=xlFilterValues
Else
xRg.AutoFilter field:=1, Operator:=xlFilterValues
End If
Err01:
'Application.ScreenUpdating = True
End Sub
I misunderstood your need in my initial response suggesting using Excel Online. You don't want a data validation list to filter itself as you type, but rather an Excel Table. Please ignore that suggestion.
Oct 19 2021 10:16 PM
Oct 20 2021 08:45 AM
It wasn't filtering the Items column because the Field index (1) in your code was working on the PO # column instead of Items (2).
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim xStr, xName As String
Dim xWS As Worksheet
Dim xRg As Range
Application.ScreenUpdating = False
xName = "Materials"
xStr = TextBox1.Text
Set xWS = ActiveSheet
Set xRg = xWS.ListObjects(xName).Range
If xStr <> "" Then
xRg.AutoFilter field:=2, Criteria1:="*" & xStr & "*", Operator:=xlFilterValues
Else
xRg.AutoFilter field:=2, Operator:=xlFilterValues
End If
End Sub
Oct 20 2021 09:29 AM
Oct 20 2021 04:50 PM - edited Oct 20 2021 04:56 PM
I suggest setting a "stop" in your code by clicking in the light gray border to the left of the statements. If successful, you should see a large maroon dot in the border.
Once the macro is invoked by typing in your textbox, code execution will stop at the statement with the stop. You may then hover over variables to see their value, or execute one statement at a time by hitting F8. If you hit F5, code execution continues until the next stop. You can drag the yellow arrow up and down in the left border to execute different statements (a little known and very useful trick when debugging).
You may also display the Immediate window (using the View...Immediate window ribbon item) and execute statements you type there; be sure to click to make the cursor at the right end of the line before hitting Enter. You may display values in the Immediate window by prefixing them with a question mark, e.g. ?xStr
These techniques help you identify the statement that isn't working right, what the problem is (most often a variable without a value or with an error value), how to remedy it, and even verify the fix.
Oct 20 2021 10:11 PM
SolutionIt isn't clear to me what you see in the two different windows when using laptop with an external monitor. Is it two different worksheets? Two views of the same worksheet?
I tested by creating a second window to display Sheet1. I sized the windows so one of them displayed just TextBox 1, while the other showed just the Table. When the textbox was in Window 2, I couldn't select it. When the textbox was in WIndow 1, the code shown below worked as desired.
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim xStr As String, xName As String
Dim xRg As Range
Application.ScreenUpdating = False
With TextBox1
xName = "Materials"
xStr = .Text
Set xRg = .Parent.ListObjects(xName).Range
If xStr <> "" Then
xRg.AutoFilter Field:=2, Criteria1:="*" & xStr & "*", Operator:=xlFilterValues
Else
xRg.AutoFilter Field:=2, Operator:=xlFilterValues
End If
End With
End Sub
Oct 21 2021 06:29 AM
Oct 21 2021 12:32 PM
Oct 22 2021 05:12 AM - edited Oct 22 2021 05:12 AM
@byundtthank you for further investigation. But what is the workaround? Can the same be achieved with other ways, say formula approach?
Oct 22 2021 04:37 PM
If you revert to event sub TextBox1_Change, you can trigger it by entering a value in its linked cell even if you cannot enter a value directly in the textbox.
Private Sub TextBox1_Change()
Dim xStr As String, xName As String
Dim xRg As Range
Application.ScreenUpdating = False
With TextBox1
xName = "Materials"
xStr = .Text
Set xRg = .Parent.ListObjects(xName).Range
If xStr <> "" Then
xRg.AutoFilter Field:=2, Criteria1:="*" & xStr & "*", Operator:=xlFilterValues
Else
xRg.AutoFilter Field:=2, Operator:=xlFilterValues
End If
End With
End Sub
May 01 2023 09:08 AM
May 01 2023 09:14 AM
May 01 2023 10:12 AM
May 01 2023 11:42 AM
Oct 20 2021 10:11 PM
SolutionIt isn't clear to me what you see in the two different windows when using laptop with an external monitor. Is it two different worksheets? Two views of the same worksheet?
I tested by creating a second window to display Sheet1. I sized the windows so one of them displayed just TextBox 1, while the other showed just the Table. When the textbox was in Window 2, I couldn't select it. When the textbox was in WIndow 1, the code shown below worked as desired.
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim xStr As String, xName As String
Dim xRg As Range
Application.ScreenUpdating = False
With TextBox1
xName = "Materials"
xStr = .Text
Set xRg = .Parent.ListObjects(xName).Range
If xStr <> "" Then
xRg.AutoFilter Field:=2, Criteria1:="*" & xStr & "*", Operator:=xlFilterValues
Else
xRg.AutoFilter Field:=2, Operator:=xlFilterValues
End If
End With
End Sub