SOLVED

Excel 365 ActiveX Textbox for "Search as you type" not working on second monitor

Brass Contributor

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.

 

24 Replies

byundt_0-1634342515063.png

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.

 

Thank you for your response. But I don't use the online excel for this.
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,. 

That would be great of you to investigate the problems I have posted originally. Here is the link to the OneDrive folder as you have requested.

https://1drv.ms/x/s!Aqv4zBSFNKaymT6dx2W3aOhdfCbX?e=SrcI5A

Thanks in advance.
Al.

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.

 

Te search is still not looking in the Items column. For example, if I type "fuse" it returns no result, i.e. it filters out all lines instead of filtering for the lines with fuses.

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
Great! You helped me to resolve my second original question. Thank you!

I still need help with my first question (below, modified):
1. The ActiveX Textbox does not work on an extended monitor to my laptop as nothing appears when I type a search value. When I hit enter or when i get out of the text box, the search value that i had typed shows up. Note that if I have the excel file on the laptop monitor it works, i.e. I can type right in the textbox and i can see it as i type.

Thanks,
Al.

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.

best response confirmed by alex_n (Brass Contributor)
Solution

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
The ActiveX text box works as desired on a main display - it can be the laptop monitor or whichever display is set as the main display when extended monitors are used. In our case, we use 1 or 2 monitors attached to our laptops. In my case it works on my laptop display as I keep the laptop monitor as the main one and when I move the excel file to other two monitors it does not work, i.e. I don't see what I type in the text box.
After further investigation, I found that ActiveX controls (I tested both textbox and combobox) cannot be selected in a secondary window. This behavior occurs in 64-bit Excel 2010, 2013 and Microsoft 365. I don't know if it occurs with 32-bit Excel.

I suspect Microsoft will consider this behavior a "feature" rather than a bug because it had the same annoying behavior in multiple Excel versions. Features don't get fixed.

When you use external monitors, they are displaying content in secondary windows. The primary window remains your laptop.

So you have two good choices: abandon the use of ActiveX controls or else make sure to access them in your laptop display. Many Excel MVPs recommend avoiding ActiveX controls because of their buggy behavior.

@byundtthank you for further investigation. But what is the workaround? Can the same be achieved with other ways, say formula approach?

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

 

Albert, I ended up using your solution. Thanks for the help!
To expand on the same topic, how would I use the key word feature where the filter filters by the criteria where more than one word is in the criteria: search the column for the data that has key words "table", "napkin", "metal". Limit to 3 key words is good enough. Thanks in advance.
Please post a link to a sample workbook showing the code you are using..
1 best response

Accepted Solutions
best response confirmed by alex_n (Brass Contributor)
Solution

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

View solution in original post