run macro after 4 digits entered

**gopcs** · 09-21-2009, 09:45 AM

I have a macro that is used several times a day. Four digits are entered in cell E5, then I press Enter, then click on an icon that has a macro assigned to it. Is there a way to trigger the macro after simply entering the four digits? Or can the macro be triggered after entering the four digits then pressing Enter? Thanks in advance.

**sweep** · 09-21-2009, 10:59 AM

Hi,

This in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("E5"), Target) Is Nothing Then Call bob
End Sub

will trigger the subroutine bob when cell E5 is changed.

**gopcs** · 09-21-2009, 02:57 PM

Thank you for the quick reply.
I must be doing something wrong. When I enter 4 digits in E5 and press Enter, nothing happens.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("E5"), Target) Is Nothing Then Call filter_site

End Sub
__________________________________________________________________________

Sub filter_site()
'
' filter_site Macro
' Macro recorded 3/28/2007 by bob
'


    data = Cells(5, 5)
    Selection.AutoFilter Field:=4, Criteria1:=data
    Sheets("MAL_Chans").Select
    Selection.AutoFilter Field:=2, Criteria1:=data
    
    Sheets("Detail_TRX").Select
    ' Selection.AutoFilter Field:=6, Criteria1:="BCCHFREQ"
    ' ActiveSheet.ShowAllData
    Selection.AutoFilter Field:=1, Criteria1:=data
        
    Sheets("Detail_CHN").Select
    ' Selection.AutoFilter Field:=5, Criteria1:="0"
    ' ActiveSheet.ShowAllData
    Selection.AutoFilter Field:=1, Criteria1:=data
    Selection.AutoFilter Field:=5, Criteria1:="0"
    
    Sheets("BTS").Select
    

End Sub

**NBVC** · 09-21-2009, 04:01 PM

The macro Dave gave you goes in the worksheet module... so you right-click the worksheet tab and select View Code, then paste his code there, in the VBE editor.