Spreadsheet change event not fired

Taras
Taras used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

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

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
It is good practice to fully qualify worksheet and range references. That's why I added the With block. Without it, a macro changing a value on that worksheet when another worksheet was active would trigger the Worksheet_Change event sub--but references to Range and Cells would point to the active worksheet rather than the one you care about.

The Intersect method in statement 11 should apply to KeyCells and Target, not Range("G4:G5") and KeyCells. Your original sub would try to timestamp cell B5 no matter which cell on the worksheet was changed (KeyCells always intersects G4:G5)--and the timestamping step would cause an infinite loop.

To avoid calling the event sub recursively when you timestamp cell B5, I turned events off for that operation, and then back on again.

Your original code was concatenating the date and time, then trying to format that string as a date & time. Rather than concatenating the date and time, you should add them, or use the Now() function as I did.

Author

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

NorieAnalyst Assistant

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

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Norie,
Good catch on the Me.
Brad
NorieAnalyst Assistant

Commented:
Brad

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

Author

Commented:
Thank you a lot,  I just added :

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


 and it works.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial