Textbox afterupdate &/or textbox change event gives continuous mouse flickering

[dss28]

I have a userform where I search a data base through a textbox by writing initial few alphabets of the word to be searched. however if I enter first first alphabet the mouse starts flickering and after much time it stops. Again if second alphabet is entered it starts flickering. this delays the input search word.

following is the code. Request help to resolve.

Private Sub TextBox31_AfterUpdate()      '  (A) search by name

If UserForm23.TextBox31.value = "" Then
ThisWorkbook.Sheets("DataSearch").Range("A2:A" & Rows.Count).End(xlUp).Offset(1).row = ""
 
End If

'=============code to avoid resizing of list box size after updating/ loading data into it ================
With ListBox3
    .Font.Size = 12             ' << redefine your font size
    .Top = 117
    .Height = 186
    .Left = 12
    .Width = 832
   
End With


 '=================to avoid the increase in heigth due to the scrolbars, font size etc.  upon loading ==================================================
Application.ScreenUpdating = True

End Sub



Private Sub TextBox31_Change()      '  (A) search by name

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Dim RowNum As Long
Dim SearchRow As Long
On Error Resume Next

With Sheet12
'If UserForm23.TextBox31.Value = "" Then
Worksheets("DataSearch").Range("A2:I9999").value = ""                 ' sheet12

Worksheets("DataDetails").Activate      ' sheet11
RowNum = 2
SearchRow = 2

Do Until Cells(RowNum, 3).value = ""           '3rd column C - name in sheet11 DataDetails

    If InStr(1, Cells(RowNum, 3).value, TextBox31.value, vbTextCompare) > 0 Then     ' results to appear in Search sheet
   
'        Worksheets("DataSearch").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value    
        Worksheets("DataSearch").Cells(SearchRow, 1).value = Cells(RowNum, 1).value   
        Worksheets("DataSearch").Cells(SearchRow, 2).value = Cells(RowNum, 2).value    
        Worksheets("DataSearch").Cells(SearchRow, 3).value = Cells(RowNum, 3).value     
        Worksheets("DataSearch").Cells(SearchRow, 4).value = Cells(RowNum, 4).value     
        Worksheets("DataSearch").Cells(SearchRow, 5).value = Cells(RowNum, 5).value     
        Worksheets("DataSearch").Cells(SearchRow, 6).value = Cells(RowNum, 6).value     
        Worksheets("DataSearch").Cells(SearchRow, 7).value = Cells(RowNum, 7).value     
        Worksheets("DataSearch").Cells(SearchRow, 8).value = Cells(RowNum, 8).value      
        Worksheets("DataSearch").Cells(SearchRow, 9).value = Cells(RowNum, 9).value     
 
        SearchRow = SearchRow + 1
    End If
    RowNum = RowNum + 1
Loop

If SearchRow = 2 Then
    MsgBox "No name was found that match your search criteria.", vbOKOnly, "Search Name"
   
    Exit Sub
End If

With Sheet12    ' datasearch sheet

Range("A1").value = "A"
Range("B1").value = "B"
Range("C1").value = "C"
Range("D1").value = "D"
Range("E1").value = "E"
Range("F1").value = "F"
Range("G1").value = "G"
Range("H1").value = "H"
Range("I1").value = "I"

ListBox3.ColumnCount = 9
ListBox3.RowSource = "A2:I99999"

ListBox3.ColumnHeads = True
ListBox3.TextAlign = fmTextAlignLeft
ListBox3.SpecialEffect = fmSpecialEffectSunken
ListBox3.ColumnWidths = "0,40,180,170,100,80,210,60,60"

'=============code to adjust the list box size after updating/ loading data into it ================
With ListBox3
    .Font.Size = 12             ' << redefine your font size
    .Top = 117
    .Height = 186
    .Left = 12
    .Width = 832
   
End With

 '=================to avoid the increase in height due to the scrolbars, font size etc.  upon loading ==================================================

End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True

End With
 
End Sub

 

[davesexcel]

The textbox change event is kicking in every time you type.
Although cool to have the code run , you have too much code for this to run effectively.

Click the forum search button at the top of this page and look for "Filter Listbox"