Worksheet Change Event- Call Macro

[davidhall]

I am trying to have a macro run automatically when the worksheet titled "Index" cell "A1" is changed. A1 references another worksheet that users change so it has a formula in it.


In the View Code of the Index sheet, I have the following code:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "A1" Then
Application.EnableEvents = False

Call IndexDataExtract

Application.EnableEvents = True

End If


End Sub



The macro that I need to run upon cell A1 changing is called IndexDataExtract.

The Vba for IndexDataExtract is placed in a module.

The code is as follows:



Option Explicit
Option Compare Text
Sub IndexDataExtract()


Dim i As Long
Dim FinalRow As Long
Dim NextRow As Integer

Application.ScreenUpdating = False
FinalRow = Worksheets("IndexImport").Cells(Rows.Count, 2).End(xlUp).Row
NextRow = Worksheets("Index").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("Index").Range("A3:D500").ClearContents
NextRow = 3
Worksheets("IndexImport").Activate
For i = 1 To FinalRow
If Cells(i, 2).Value = Worksheets("Index").Range("A1").Value Then
Cells(i, 2).Resize(1, 4).Copy Destination:=Worksheets("Index").Cells(NextRow, 1)
NextRow = NextRow + 1
End If
Next i
Worksheets("Index").Activate

'Filtering Column C which is the index date, from the most recent to oldest

Rows("3:3").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Index").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Index").AutoFilter.Sort.SortFields.Add Key:=Range( _
"C3"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Index").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter

Application.ScreenUpdating = True

End Sub


I have tried placing the IndexDataExtract code in ThisWorkbook and in the view code section with the private sub code but cant seem to get it to work.

Any suggestions?

 

[VoG]

If A1 houses a formula try like this

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("A1").Value <> OldVal Then
    Application.EnableEvents = False
    OldVal = Range("A1").Value
    Call IndexDataExtract
    Application.EnableEvents = True
End If
End Sub

the IndexDataExtract code should go in a regular module.

 

[davidhall]

Ok. Does the code for the Sub IndexDataExtract need to be placed in a module, in ThisWorkbook, or in the worksheet Index?

 

[VoG]

In a regular module. In the VBE Insert > Module.

 

[davidhall]

When I the value is update an error appears.

It says:

Compile Error
Expected Variable or Procedure, not module.

It is pointing to the following line.

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("A1").Value <> OldVal Then
Application.EnableEvents = False
OldVal = Range("A1").Value
Call IndexDataExtract
Application.EnableEvents = True
End If
End Sub

 

[VoG]

Have you deleted IndexDataExtract from all the wrong places where you had it?

You haven't named a module IndexDataExtract?

 

[davidhall]

Yes,
It has been deleted from ThisWorkbook and from the worksheets "Index" view code area.


Currently, I have a module named IndexDataExtract and the vba you provided in the view code area of the Index worksheet

 

[VoG]

Rename the module IndexDataExtract to ModIndexDataExtract or similar.

 

[davidhall]

That worked great. It is not functioning properly.

By any chance, do you know of a way to make it run quicker? It seems to take 4-5 seconds. Do I need to mess around with the screenupdating aspect?

 

[davidhall]

Sorry, I meant to say "It is now functioning property"