Clever Geek Handbook

Running VBA macro when editing a cell

I am just trying to write a search macro on an excel sheet. How can I run a macro dynamically WHILE editing a cell. When writing to a cell, the search macro must run in the background, with each character added or removed not only at the end. Worksheet_Change(ByVal Target As Range)

runs only after editing is complete (the return was deleted or another cell was selected).

Thank.

+3


source to share


2 answers


You can not. The code engine will not work while Excel is in edit mode. You want the user to enter text into something other than a cell — like a control on a sheet or a control on a custom form.



+5


source


Thanks to Dick Kuslaika for answering my question and putting me on the right track.

Here is the definitive solution for those with similar requirements. It mainly works with ActiveX TextBox to enter search string. Macro than search in the search scope for all records that contain the search string. All other completed lines in the search box will be hidden. This works right away when written to the TextBox. Thus, when characters are removed in the search string, hidden strings will appear immediately.



Private Sub TextBox1_Change()
 Dim searchArea As Range, searchRow As Range, searchCell As Range
 Dim searchString As String
 Dim lastRow As Integer

 Application.ScreenUpdating = False
 searchString = "*" & LCase(TextBox1.Value) & "*"

 ' unhide rows to have the full search field when editing
 Rows.Hidden = False

 lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
 Set searchArea = Me.Range("A5", "A" & lastRow) 'Me.Range("A5").End(xlDown))
 searchArea.EntireRow.Hidden = True

 For Each searchRow In searchArea.Rows
   For Each searchCell In searchRow.Cells
     If LCase(searchCell) Like searchString Then
       searchRow.Hidden = False
       Exit For
     End If
   Next searchCell
 Next searchRow

 Application.Goto Cells(1), True
 Application.ScreenUpdating = True

End Sub

works like a charm.

+1


source






More articles:


All Articles