Autorun Macro Upon Data Entry

excelnovice05

Board Regular
Joined
Jan 4, 2005
Messages
66
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This code will run your AutoFilter macro when your range in column A is changed.
Code:
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

Legend
Joined
May 15, 2003
Messages
29,536
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
This code goes in the SHEET module, not standard module.
Right click sheet tab you want this applied to, view code
paste there.

Code:
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

Board Regular
Joined
Jan 4, 2005
Messages
66
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,256
Messages
5,600,556
Members
414,388
Latest member
Pkmep4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top