Autorun Macro Upon Data Entry

[excelnovice05]

Hi Excel Gurus,

I have what I hope is a simple question. I am trying to run the macro below ("AutoFilter()")when information is entered into cells A1:A1000. How can I autorun this macro.

Sub AutoFilter()
'
' AutoFilter Macro
'
' Keyboard Shortcut: Ctrl+Shift+A

I look forward to hearing back from you all.

Thanks,
The Novice

 

[Datsmart]

This code will run your AutoFilter macro when your range in column A is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
'   Only look at single cell changes
If Target.Count > 1 Then Exit Sub
'   Set Target Range
Set rng = Range("A1:A1000")
'   Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
'   Action if Condition(s) are met
    Call AutoFilter
End Sub

Put code in WorkSheet Module
Right click sheet tab
Click "View Code"
Paste the code into the panel that opens. ("WorkSheet" Module)
Alt-Q to quit the VBA Editor.

 

[Smitty]

Provided you data is entered manually you can use a change event:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("A1:A100")
             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Only look at that range</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            <SPAN style="color:#00007F">Call</SPAN> AutoFilter
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that it's not a real good idea to name your code after an actual VBA procedure.

HTH,

Smitty

 

[Jonmo1]

This code goes in the SHEET module, not standard module.
Right click sheet tab you want this applied to, view code
paste there.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A1000") Is Nothing Then
    If Target.Count <> 1 Then Exit Sub
    Application.Run "AutoFilter"
End If
End Sub

 

[excelnovice05]

Thanks for the answers. I was able to mix two of the codes and it worked like a charm. Here's what I put together:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Set Target Range
Set rng = Range("A1:A1000")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
Application.Run "AutoFilter"
End Sub

Thanks again. I really appreciate all of your help with this.

Regards,
The Novice