asked on

Spreadsheet change event not fired

I have issue with change event in Excel Worksheet.
Excel 2016.

In my workbook I have several worksheet.
On first one I have two cells that I am using as parameters for my data source query at the same time when I change value in one of two cells I want to use that as trigger to pull date and time when this happened and put that on spreadsheet.

Here is code of Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("G4:G5")

If Not Application.Intersect(KeyCells, Range("G4:G5")) Is Nothing Then

' we will put time stamp in cell B5
Cells(5, "B").Value = Date & " " & Time
Cells(5, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"

End If

End Sub

Event is fired only when I first time open workbook and change value in those two cells.
After that event is not fired data source is refreshed and I got new data in my spreadsheet but change event is not triggered for date time display.

What could be issue.

byundt

Try the code like this:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    With ActiveSheet
        Set KeyCells = .Range("G4:G5")
    
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
    
            ' we will put time stamp in cell B5
            Application.EnableEvents = False
            .Cells(5, "B").Value = Now()
            .Cells(5, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
            Application.EnableEvents = True
        End If
    End With

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Taras

ASKER

I tried this and it is working when I am on first one worksheet- the worksheet that has that code in. However when I switch to next worksheet and click "Refresh All" or Refresh I got error on first worksheet:

Run Time error '1004':
Method 'intersect' of object '_Application' failed.

Norie

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.

    With Me
        Set KeyCells = .Range("G4:G5")
    
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
    
            ' we will put time stamp in cell B5
            Application.EnableEvents = False
            .Cells(5, "B").Value = Now()
            .Cells(5, "B").NumberFormat = "m/d/yyyy h:mm AM/PM"
            Application.EnableEvents = True
        End If

    End With

End Sub

Open in new window

byundt

Norie,
Good catch on the Me.
Brad

Norie

Brad

I'm not sure if it'll help - I think there might be something else going on.

Taras

ASKER

Thank you a lot, I just added :

If ActiveSheet.CodeName = "Sheet1" Then
With ActiveSheet......

and it works.