Forum Discussion
Solved
Excel 365 ActiveX Textbox for "Search as you type" not working on second monitor
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.
It 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
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.
- After giving it a try, I see that it does not answer my question: it does not instantly filter as I type.
"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,.
- Thank you for your response. But I don't use the online excel for this.
