cell entry change to start a macro

[Rob Mann]

please can someone help.
in cell A1 i have a drop down list with 4 peoples names in. when someone picks a name (cell changes from blank, to a name) i want to run a macro. how do i do it. ppppplllllleeeeeeaaaaassssssseeeeee help

 

[Rob Mann]

I read your link and changed the target to a range as shown below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) Range"G3,G52" And Target.Value <> "" Then
Call ThisWorkbook.SendEMail1
ElseIf Target.Address(False, False) = "M3" And UCase(Target.Value) = "YES" Then
Call ThisWorkbook.SendEMail2
End If
End Sub

but i keep getting a compile error message saying : then or goto.
i'm in the very early stages of learning about vba. please bare with me..

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G3:G52")) Is Nothing And Target.Value <> "" Then
    Call SendEMail1
ElseIf Target.Address(False, False) = "M3" And UCase(Target.Value) = "YES" Then
    Call SendEMail2
End If
End Sub

 

[Rob Mann]

aaarrrhhhh

it now comes up with a compile error. Sub or Function not defined

 

[VoG]

Do you have subs called SendEmail1 and SendEmail2 in a standard module (like Module1, not ThisWorkbook or a sheet's code module) in the same workbook?

 

[Rob Mann]

ive got subs called "SendEmail1" & "SendEmail2" in "this workbook"

do i need to copy them into "module 1" ?

 

[VoG]

Yes, that is where they should go.

 

[Rob Mann]

thank you.. just one last thing.

i also need it to call SendEmail2 if any of M3:M52 are changed to "YES".
i tried changing the elseif part to the same as the upper part but with no success.

I need to get on a course, i know.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G3:G52")) Is Nothing And Target.Value <> "" Then
    Call SendEMail1
ElseIf Not Intersect(Target, Range("M3:M52")) Is Nothing And UCase(Target.Value) = "YES" Then
    Call SendEMail2
End If
End Sub

 

[Rob Mann]

you are a genius. thank you so much...

 

[Rob Mann]

could you suggest a formula for this:

in cells "A2 : A52" there will be dates loaded. In cell "A1" i have added =now(). if any of the cells "A2 : A52" are the same date as =now() i want a "yes" to appear in the column "B"

for example if =now() (31/10/11) and cell "A14" is also "31/10/11, i want a yes to appear in "B14"