4

I simply try to write a search macro in an excel sheet. How can I start a macro dynamically DURING editing a cell. When writing in a cell the search macro should run in the background with every character added or deleted not just at the end. Worksheet_Change(ByVal Target As Range) only starts when editing is finished (return was hit or other cell was selected).

Thanks.

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
JFS
  • 2,992
  • 3
  • 37
  • 48
  • 2
    Actually you can try by using api if you are willing to go that route. See http://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell – ZAT Nov 10 '14 at 17:02

2 Answers2

7

You can't. The code engine won't run while Excel is in Edit mode. You have to have the user enter the text in something other than a cell - like a control on the worksheet or a control on a userform.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • Ok, thanks and good to know. But what is the difference with using a control in the worksheet? Do you think about a textfield? – JFS Nov 10 '14 at 21:42
  • If you use an ActiveX Textbox, you can use that control's Change event that will fire with every letter typed, which seems more in line with what you want to do. It just gives you a 'while you're typing' event that a cell doesn't. – Dick Kusleika Nov 10 '14 at 22:20
  • Hello Dick, thank you very much for your answer. It took me a while but I got it running with your hints. I'll answer my question self just in case somebody else is looking for similar solutions. – JFS Nov 11 '14 at 11:31
  • Actually events fire and code executes even if user is editing a cell. While a cell is being edited, `Application` object (and all of it's members) just becomes unresponsive, thus you can't get a value of that cell. – omegastripes Apr 20 '17 at 23:06
2

Thanks to Dick Kusleika for answering my question and to put me on the right track.

Here is the final solution for anybody having similar demands. It basically works with an ActiveX TextBox to enter the search-string. The macro than is looking in the search-area for all entries containing the search-string. All other filled rows within the search-field will get hidden. This works right away when writing into the TextBox. So, when deleting characters in the search-string the once hidden rows will appear right away if appropriate.

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.

JFS
  • 2,992
  • 3
  • 37
  • 48