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

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then Call MyMacro
End Sub

Change MyMacro to the name of your macro.

 

[Rob Mann]

that almost works perfectly. thank you.
is there a way that it doesnt run the macro when "A1" entry is deleted. (when there is a name in the cell, and that job is complete we need to delete the contents of that cell but without the macro being called)

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" And Target.Value <> "" Then Call MyMacro
End Sub

 

[Rob Mann]

thank you, that works just as i wanted it to. just one last thing, iam using a macro that i've written to send out several emails at once. I want to populate the entry of a certain cell reference in the email. The code below shows the code i am using.

Msg = Msg & "A new entry has been added to the complaint spreadsheet, the new job number is " & Cells(r, 2) & "please read the comms and action immediately" & vbCrLf & vbCrLf

i know the (r, 2) refers to column B.
A=1 B=2 C=3 etc.
If i wanted to refer to cell "B3" instead of all the column B entries, how would i modify my current code?

Thank You

 

[VoG]

Try

Msg = Msg & "A new entry has been added to the complaint spreadsheet, the new job number is " & Range("B3").Value & "please read the comms and action immediately" & vbCrLf & vbCrLf

 

[Rob Mann]

thats perfect!!! thank you.

my cell reference "M3" has a drop down box and the options are "yes" and "no". I would like before, to call the SendEmail macro if the entry changes to "Yes" but no "no" and blank. please could you help?

 

[VoG]

Assuming that this is an additional condition try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" And Target.Value <> "" Then
    Call MyMacro
ElseIf Target.Address(False, False) = "M3" And LCase(Target.Value) = "yes" Then
    Call MyMacro
End If
End Sub

 

[Rob Mann]

Good Morning.
the code below works great.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" And Target.Value <> "" Then
Call MyMacro
ElseIf Target.Address(False, False) = "M3" And LCase(Target.Value) = "yes" Then
Call MyMacro
End If
End Sub

i have now developed my spread sheet and i want it to call my "email" macro if cell "b3","b4","b5" through to "b53" changes. any ideas?

 

[VoG]

Perhaps look here http://www.rondebruin.nl/mail/change.htm