Not running macro on automatic calculation

Hi

I have a cell "H7" which changes from FALSE to TRUE if a check box is ticked, upon doing this i want to email an address located in "L7"

I can get it to do this but only by going into the cell and pushing enter, to come out of the cell, i need it to do it without having to go into the cell, what is wrong in this code?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("H7")) Is Nothing Then Mail
End Sub

Sub Mail()
Dim OutlookApp As Object
Dim Mess As Object, Recip
Recip = [L7].Value
Set OutlookApp = CreateObject("Outlook.Application")
Set Mess = OutlookApp.CreateItem(olMailItem)
With Mess
.Subject = "Task Completed"
.Body = "XXXXXX XXXXXX has completed task" & [B7].Value
.To = Recip
.Display
.Send
End With
End Sub

Also the email doesnt pick up & [B7].Value

Any one got an ideas?

Thanks

Dan

Why not use the Checkbox Click event (activex control) for the call to Mail based on the checkbox value? The code depends on whether you used a forms control or an activex control.

I see no reason for the [B7].value to not work. Most likely, the value is "". Of course the B7 is for the activesheet as is Range("B7").Value.

p.s it wasnt the value in b7 i was after it was c7 so that works fine now, haha

Hi

Thanks i got the checkbox click event to do what i want it to rather than the change event, so thats good, thanks, but it executes the macro on uncheck too? is there a way to stop this?

Sounds like you are using a Forms checkbox control. Obviously, your macro that you assign to it would do it. Do you want an example?

Nope its definately an activex control, the only way i can see it working is if i do an IF arguement followed by a call module...

I wouldnt mind if you could please?

Right, this is now more wrong than i originallly thought,

1st. It is emailing everytime i select a new cell
2nd. It isnt picking up the next C column value
3rd. I have to click in column H or another cell before the code is run

I have attached the sheet, if you wouldnt mind taking a look for me???

I would really appreciate any help please

Thanks

I have left the design mode on so it doesnt keep emialing me every time a cell is clicked on

Dan

Ok great thank you, i will see what i can do with that, im not an expert user on vba and really only know some basic codes, but my reason for trying to do this, is because i wanted to use Sharepoint, but my IT department wont allow the Sharepoint Foundation on our network, so have to try and do something in excel, which requires regular updates to the users of this task sheet.

I appreciate your help

I couldnt quite understand the code you posted yesterday, i do however have the code working, but on every time i click on a new cell, can i only get it to do it once? i thought loop might be able to do this but doesnt seem too.....

Thats great!!

It works all except one thing...... When i check a box say in row 7, it is picking up the email address from row 6, and the task from row 6 too, can you add +1 on to the row?

i.e. Recip = Cells(lRow + 1, "L").Value
Cells(lRow + 1, "C").Value

Dont worry i added the +1 and it works fine thank you very much for your help

Ok thank you, i will give that a try, thanks for looking at it for me

You could but I suspect that your checkbox is not quite in the cell. It gets the row from the TopLeft of the checkbox clicked.

Ah ok, that would make sense

I have slightly changed that bit of code to

Sub Mail()
Dim OutlookApp As Object
Dim Mess As Object, Recip
Dim lRow As Long
Dim cb As CheckBox
Set cb = ActiveSheet.CheckBoxes(Application.Caller)
If cb.Value > 0 Then
lRow = cb.TopLeftCell.Row
Recip = Cells(lRow + 1, "L").Value
Set OutlookApp = CreateObject("Outlook.Application")
Set Mess = OutlookApp.CreateItem(olMailItem)
With Mess
.Subject = "Task Completed"
.Body = "Daniel Schofield has completed task" & " " & "-" & " " & Cells(lRow + 1, "C").Value
.To = Recip
.Display
.Send
End With
End If
End Sub

Where it is >0 so checked it sends the email, if it is unchecked it doesnt send an email and it doesnt send an email on me clicking in another cell