CODEWITHSUNDEEP
excelvba
user2161965
user2161965

Reputation: 37

Get Worksheet_change event to execute macro automatically

I have a macro (ApplyFilter) that filters through many worksheets based on a date I enter into cell B1 on another worksheet (Grand Totals). That macro is:

Sub ApplyFilter() 'Filters all worksheets except worksheet1 for date entered into _
                  'Grand Totals!B1
Dim WS_Count As Integer
Dim I As Integer
Dim FilterRange As Variant
FilterRange = Range("'Grand Totals'!B1")
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
    For I = 2 To WS_Count
    Sheets(I).Select
    ActiveSheet.AutoFilterMode = False 'Remove any existing filters
    Worksheets(I).Range("A2").AutoFilter Field:=1, Criteria1:=Range("'Grand Totals'!B1").Text
    Next I
Sheet1.Activate
End Sub

When I execute this macro manually, it executes and filters as it should. However, when I call this macro from another sub:

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then _
    Call ApplyFilter
End Sub

I get a "Macros" window that offers a list of macros available. I can select the "ApplyFilter" macro and click Run and the Macro executes and filters the worksheets as I desire.

I found many references to automatically executing a macro from within a sub, but none refer to the "Macros" window, from which I now must select the macro to run. Instead, when I enter a date in cell B1 of the "Grand Totals" worksheet and hit enter, sub worksheet_change(ByVal Target As Range) should automatically call "ApplyFilter" and apply the date filter to the many worksheets.

I have created a button and used Button_Click to call "ApplyFilter", and all is well. But, it seems more intuitive to enter the date and then press Enter to execute the macro. I could put up with the Button_Click method, but I'm trying to learn VBA firstly, and I'm just stubborn enough to want to learn how to make it work, and I do not want settle just for what will work.

Upvotes: 2

Views: 4557

Answers (1)

brettdj
brettdj

Reputation: 55712

Sheet Code must be in Grand Totals sheet

  • right click your Grand Totals sheet tab
  • View Code
  • Ensure the code below is pasted in here
  • back to Excel

Grand Totals Sheet Code

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then Call ApplyFilter
End Sub

more efficient filter code

Sub ApplyFilter()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
      If ws.Name <> "Grand Totals" Then
        ws.AutoFilterMode = False
        ws.Range("A2").AutoFilter Field:=1, Criteria1:=Range("'Grand Totals'!B1").Text
      End If
Next
End Sub

Upvotes: 2

Related Questions