VBA : Worksheet_Change - changes to a dynamic range

daniB · Apr 20th 2004

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 -

Dani

Anonymous · Apr 20th 2004

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.

Try:

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

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

daniB · Apr 22nd 2004

Oh Dear it still doesnt work!

Im baffled!

thanks tho!

Richie(UK) · Apr 22nd 2004

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.

Code

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


    ThisWorkbook.Names.Add Name:="thirdparty", _
        RefersToR1C1:="=OFFSET('Sheet1'!R1C1,0,0,COUNTA('Sheet1'!C1)-1,9)"
    Set thirdparties = Range("thirdparty")


    If Union(Target, thirdparties).Address = thirdparties.Address Then
        If MsgBox("Confirm EMail notification??", vbQuestion Or vbYesNo) = vbYes Then
            MsgBox "OK"
        Else: MsgBox ("Email notification Cancelled!!")
        End If
    End If


End Sub

Display More

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.)

daniB · Apr 22nd 2004

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!

tvm

VBA : Worksheet_Change - changes to a dynamic range

Participate now!

Share