Help needed with a text field that starts vba command that filters

[MSchädler]

I need help for my following issue which I haven't been able to solve.
.
I have a defined text field in my sheet and when entering a text (characters) or numbers (digits) it starts executing (filtering) right away. That takes some time.

Is there a way to define by the user when the vba command should start executing?

The macro i defined is as follow:

Private Sub Suchfeld_Change()
If Sheets("Uebersicht").FilterMode = True Then
Sheets("Uebersicht").ShowAllData
End If

Application.EnableEvents = False
Application.ScreenUpdating = False

Sheets("Uebersicht").Unprotect Password:="XXX"
Sheets("Uebersicht").Cells(2, 2).Value = "*" & Suchfeld.Text & "*"
Sheets("Uebersicht").Cells(3, 3).Value = "*" & Suchfeld.Text & "*"
Sheets("Uebersicht").Cells(4, 4).Value = "*" & Suchfeld.Text & "*"
Sheets("Uebersicht").Cells(5, 5).Value = "*" & Suchfeld.Text & "*"
Sheets("Uebersicht").Cells(6, 6).Value = "*" & Suchfeld.Text & "*"
Sheets("Uebersicht").Cells(7, 7).Value = "*" & Suchfeld.Text & "*"
Sheets("Uebersicht").Cells(8, 8).Value = "*" & Suchfeld.Text & "*"
'Sheets("Uebersicht").Cells(9, 9).Value = "*" & Suchfeld.Text & "*"
'Sheets("Uebersicht").Cells(10, 10).Value = "*" & Suchfeld.Text & "*"


' filtering columns M-U according to search criteria in B1-J10
Sheets("Uebersicht").Columns("M:S").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("B1:J10"), Unique:=False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

For additional help I can give you the link to my drop box test sheet, where you can see the layout.

Any help is appreciated.
Marc

 

[V_Malkoti]

You can add a button next to that text field and move this code into that button's click event code.

 

[MSchädler]

Hello V_Malkoti
Thanks for this proposal. Unfortunately I don't know how to set up such a link.
My macro starts executing as soon as you enter a lettre or digit in the text field.
In your proposal, the command would have to tell to fetch the input in the Text field and execute filtering.

Here is the link to my drop box for viewing the test sheet. https://www.dropbox.com/s/5g5c4vl1q3au1dx/MrExcel_Tracking_ProgChanges_Test.xlsm?dl=0
Perhaps you can tell me how to set it up.
Thanks
Marc

 

[V_Malkoti]

I can't download files at work, so I'll have to look at that on my home computer later today.

Meanwhile you can try that yourself. Here are the steps:
1. Switch to design mode (Developer -> Design Mode)
2. Insert an ActiveX command button (Developer -> Insert -> ActiveX Controls -> Command button)
3. Right click on the button -> Properties and set its name (optional)
4. Right click on button and click View Code; VBA window will pop up showing a blank _Click event procedure for the button.
5. Move all the lines of code from your existing Suchfeld_Change to that command button's _Click procedure.

Now, the button's click event code has your existing code which is reading search field and applying filter with that value. That code will be executed whenever user clicks on the command button, which is after the user has entered all characters in the text field.

 

[MSchädler]

Hello V_Malkoti
Thanks again for your input. I will try later on to adapt my sheet according to your steps mentioned above.
I will give you a feedback tomorrow.
Meanwhile thanks again and have a good time.
M.

 

[MSchädler]

Good day V_Malkoti
I have defined the activ-X button as described by you and the sheet works fine.
Thanks for your help. This is what I was looking for.
Thanks again.
M