Worksheet_Change event not working

[huiettcm]

I have a simple search and filter macro called FILTER_SHEET I have tied to a command button that works fine. It uses a keyword placed into cell C2. I would like any change to the cell to trigger the FILTER_SHEET (enter, tab, arrow, any exit off the cell). I've declared keyword as a public string in a seperate module. What I have isn't working, obvi. Any assistance would be greatly appreciated. I have the following change event:

Private Sub Worksheet_Change(ByVal target As Range)

If Not Intersect(target, Range("C2")) Is Nothing And Range("C2").Value = "keyword" Then

Call FILTER_SHEET

End If

End Sub

 

[Chihiro]

This...

Range("C2").Value = "keyword"

is the likely issue.

You are comparing [C2].Value against literal string "keyword". If it isn't, then code will not proceed.

FYI - Please nest the code in Code tag (I've fixed it this time).

 

[p45cal]

It works here if there is the literal keyword in cell C2.
Perhaps it should be:

If Not Intersect(target, Range("C2")) Is Nothing And Range("C2").Value = keyword Then

(no quote marks since it's a variable)
Check that the change event is triggering too; place a breakpoint in the first line of the change event code (F9 on the keyboard).
If it's not, then check the status of Application.EnableEvents (it should be True).

 

[Chihiro]

Since it's filter. I assume there's more than one valid keyword(s)

It really depends on how keyword(s) is defined.

If it's string...
Ex: keyword = "keyword1, keyword2, keyword3,..."
Then Instr() function should be used.

If it's list (i.e. array) of valid strings. Either Application.Match() or Filter() should be used.

I'll wait for op to respond and/or upload sample workbook demonstrating his need.

 

[huiettcm]

that makes sense that it was the literal string keyword. Also, thanks for fixing that code tag, Chi.

keyword is defined as a public string. It can be any word. It takes whatever the word in cell C2 is and compares it to each cell that contains data. If it finds a match, it puts the results into an array and filters the worksheet based on the array. So Any change in [C2] should trigger the FILTER_SHEET. I have to leave but I can add the Sub FILTER_SHEET () code tomorrow, if it would be helpful.

 

[p45cal]

I'm beginning to suspect that:

If Not Intersect(target, Range("C2")) Is Nothing Then

may be all that you need in that line.
Yes I think your filter code would be useful to see what you're doing.

 

[huiettcm]

There are a few procedures that all go together. These aren't the only macros in the workbook so that's why things are organized into subs like this. Also, most of my variables are public and declared in module 2 but I'll place them at the top of the code block. If I missed a variable or something retyping this, I appologize. I have the first 4 rows of the worksheet frozen with my headers in row 4. Above that are my command buttons and "search" cell, C2 is labeled and designated for the search string. Here's the applicable procedures:

Public datarange As Range
Public originalcell As Range
Public mytable As Range
Public count As Integer
Public originalrow As Integer
Public originalcolumn As Integer
Public lastrow As Long
Public keyword As String

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("C2")) Is Nothing And Range("C2").Value = keyword Then
Call FILTER_SHEET
End If
End Sub


Sub FILTER_SHEET()

ActiveWindow.FreezePanes = False

Dim searchArea As Range
Dim searchResults() As String

Call SET_VARIABLES

If keyword = "" Then
Call KILL_FILTER
Exit Sub
Else

Range("C4").AutoFilter Field:=3

keyword = "*" & Range("C2").Value & "*"
lastrow = Cells(Rows.count, "c").End(xlUp).row

Set mytable = Range("a4:h" & lastrow)
Set searchArea = Range("b4:h" & lastrow)
ReDim searchresults(1)
searchResults(1) = ""

For Each i In searchArea
    If UCase(i.value) Like UCase(keyword) Then
    ReDim Preserve searchResults(count)
    searchResults(count) = Range("c" & i.row).Value
    count = count+1
    End If
    Next i

lastrow = Cells(Rows.count, "c").End(xlUp).row
Set mytable = Range("a4:h" & lastrow)

mytable.Autofilter Field:=3, Criteria1:=searchResults, Operator:=xlFilterValues

Rows(5).Select
ActiveWindow.FreezePanes = True

Cells(originalrow, originalcolumn).Select
End If
End Sub


Sub KILL_FILTER()
ActiveWondow.FreezePanes = False

Cells(2,3).ClearContents
Call SET_VARIABLES

On Error GoTo Err

ActiveSheet.ShowAllData

Err:

Call REFREEZE_PANES

Cells(originalrow, originalcolumn).Select
End Sub


Sub SET_VARIABLES

originalrow = ActiveCell.row
originalcolumn = ActiveCell.Column
keyword = Cells(2,3).Value

Set originalcell = Cells(originalrow, originalcolumn)
Set datarange = Range(Cells(originalrow, "a"), Cells(originalrow, "h"))
End Sub


Sub REFREEZE_PANES()

Rows(5).Select
ActiveWindow.FreezePanes = True
ActiveSheet.AutoFilterMode = False
End Sub

 

[huiettcm]

I also tried using

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("C2")) Is Nothing Then
Call FILTER_SHEET
End If
End Sub

to no avail. It just moves the active cell and doesn't trigger the procedure.

 

[p45cal]

I made minimal changes to your code in the attached, marked by comments such as new and different which you can search for.

 

[huiettcm]

Do all the change events have to go into the MS Excel Objects/Sheet1 (or whatever sheet they apply to) and not into the modules? I didn't know that. This works perfectly. Thank you so much!!!!!

 

[p45cal]

Do all the change events have to go into the MS Excel Objects/Sheet1 (or whatever sheet they apply to) and not into the modules?

Largely, yes. However, if you want the code to apply to many sheets you can save yourself some typing by having the likes of:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Sh.Range("C2")) Is Nothing Then 'different
  Call FILTER_SHEET
End If
End Sub

in the ThisWorkbook code-module.

but this will try to work on any sheet which you probably don't want, but with an additional line or two you can restrict its activity to only certain sheets, or exclude some sheets, eg.:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Master Sheet" Then 'all sheets except this one are candidates for processing.
  If Not Intersect(Target, Sh.Range("C2")) Is Nothing Then
    Call FILTER_SHEET
  End If
End If
End Sub

 

[huiettcm]

That makes a lot of sense. And explains why the code runs without specifically being called. I suppose if there are a lot of events on multiple sheets, it would be easier to have them all in one place with the sheet names applied (in addition to applying the same event to multiple sheets). I'm liking this forum much better than SO. lol.