VBA : Worksheet_Change - changes to a dynamic range

  • Hello all! - only 3 more days til the weekend!

    I have some code where by if the user changes the value of a cell within a dynamic range an email is delivered.

    First of all the code below doesnt work!! I thought i was on the right track! -

    Second of all, in an ideal world I would like to send only one email when one or more changes have been made to the cells in this dynamic range. I know this is possible but my VBA skills are very limited! Can anyone help here?

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim thirdparties As Range

    ThisWorkbook.Names.Add Name:="thirdparty", RefersToR1C1:="=OFFSET('SDF Approval Summary'!R1C1,0,0,COUNTA('SDF Approval Summary'!C1)-1,9)"

    Set thirdparties = Range("thirdparty")

    If Target.Address = thirdparties.Address Then
    If MsgBox("Confirm EMail notification??", vbQuestion Or vbYesNo) = vbYes Then
    ThisWorkbook.SendMail "[email protected]", "A 3rd Party CCN has been changed"
    Else: MsgBox ("Email notification Cancelled!!")
    End If
    End If

    End Sub

    Naturally very appreciative of any help -


  • Onlyhad time for a quick look, but one thing is that your line:

    If Target.Address = thirdparties.Address Then

    will always be false unless thirdparty is a single cell.


    If Union(Target, thirdparties).Address = thirdparties.Address Then

    This will be true if and only if Target overlaps with thirdparties.

  • Hi dani,

    OK, let's take it a step at a time. I take it the event itself isn't firing, so let's look at that - we'll look at the 'one time only' request and the email sending afterwards.

    Did you amend the event as suggested by Tom? I tested the following in the sheet code for Sheet1 and it seemed to work OK.

    What does your revised code look like?

    (Forgive me asking, but you do have it in the right place don't you? ;;) If you are not sure, right-click on the sheet tab in question and select 'View Code' - the window that you are taken to is where the code should be placed.)

  • Hellooo

    Ok i tried the exact code in a new spreadsheet and it works,
    I try it in my workbook and the following message appears:

    Runtime error 1004 method 'range' of object '_worksheet' failed

    very weird!!! - I have EXACTLY the same in my current workbook as I had it when it worked in the new spreadsheet!

    its a strange thing!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!