I am needing a macro that emails based on a cell value. When the cell is 25% I want an email that says alert 25% and then again at 50% and 75% and 90%. I found a start online and I got it to work on a new blank book but when I put it in the spreadsheet I need to use it on for some reason it doesn't work. The book I am using it on takes two cells and =(D4/C4)
Please Login or Register to view this content.
Last edited by AliGW; 09-27-2021 at 09:29 AM.
Possibly something along this line... (changes in red)Please Login or Register to view this content.
I put this in there and then I changed the format of the cell to number and it worked now it doesn't I am so confused.
Please Login or Register to view this content.
Last edited by AliGW; 09-27-2021 at 09:28 AM.
What do you mean by? What doesn't work?...it worked now it doesn't...
I copied what you sent me and put it in the code and seemed like it was working but now it doesn't work I can't get it working for nothing?
Please Login or Register to view this content.
Last edited by jimcclure; 09-27-2021 at 09:23 AM.
I ask again... What isn't working? Does the code stop and give an error? If it just doesn't send an email, check if the target range was the range you changed. And if true, did the cell contain a numeric value?
Also, if the cell in question contains a formula and it's value changes, it will not trigger the worksheet change event.
Well for some reason it worked once. It isn't opening up the email and prompting for send like it had done. The cell F4 is a formula making a percentage out of numbers. Is there a different way to go about this then?
Cell F4 contains =D4/C4
Last edited by jimcclure; 09-17-2021 at 01:12 PM.
It doesn't see a change because the formula in the target cell has not changed. Try targeting a cell where its contents does change and affects the cell in question.
You might wish to use the Worksheet_Calculate event, assuming the formula is only in the one worksheet.
Last edited by dangelor; 09-19-2021 at 01:45 AM.
Where do I put Worksheet_Calculate to make this work?
In that worksheet's code module. Right click on the worksheet's tab and select 'View Code'.
I put Worksheet_Calculate in the code and still nothing.
Here is a simple example...Please Login or Register to view this content.
Place this in the worksheet's code module......and this in a standard code module.Please Login or Register to view this content.
Please Login or Register to view this content.
That works thank you so much!!!
Glad to help!
One Last questionIf I want that range to be F4:F63 is there something special I thought I could plug that in but it doesn't like that either.
Please Login or Register to view this content.
Last edited by jimcclure; 09-27-2021 at 09:24 AM.
Yes F4 thru F63 have percentage formulas that I wanted to have it email me when they reach 25% thought there was a way to plug that in easily but I was wrong once again.
Try this in the worksheet's code module......and these in a standard code modulePlease Login or Register to view this content.Please Login or Register to view this content.
I did what you said and it seems that anytime I add anything or change anything it automatically creates an email wanting to send 25% notification
Post a sample workbook, so I can do some live testing.
Deleted Please see next
Last edited by jimcclure; 09-27-2021 at 07:11 AM.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
Maybe this works I had issues last time and think I figured out what they wanted from me.Please Login or Register to view this content.
If you look at Dangelor's posts you will see that the VBA code is wrapped. It would be good of you could go back to your previous posts and wrap your VBA code by using the explanation in Post #23
I am really confused. I am not seeing what I am doing wrong and not sure what I need to fix?
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(Note: this change is not optional. As you are quite new here, I have done it for you today, however you must do this yourself next time.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Sorry for the delay, didn't want to step on any moderator's toes. Been yelled at too many times...
Put this in the worksheet's code module...and these in a standard modulePlease Login or Register to view this content.Please Login or Register to view this content.
No worries I just wasn't sure what I was doing wrong so I was having a hard time understanding. Still not sure what I was doing. I tried those and it seems like it is generating an email anytime I type something. I typed 0 in one of the cells and it put up an email for sending.
My post just above yours does not 'stop' the thread - see the last line.Sorry for the delay, didn't want to step on any moderator's toes. Been yelled at too many times...
By "those", do you mean the code in #28 that I posted earlier? If so, please post a sample workbook (not the link above that only opens in the web version of Excel). Follow the directions in the yellow banner at the start of the thread.Originally Posted by jimcclure
BTW, I do not accept friend requests from anyone.
Just playing it safe... I'm a bit dyslexic.My post just above yours does not 'stop' the thread - see the last line.
I have attached it I believe correctly.
This seems to work...
Ok that is exactly what I am wanting. So can I copy part of that code and make it do .25 , .50, .75, .90? Is that simple to copy and repeat?
Yes, the smaller subroutine.
Just like this?Please Login or Register to view this content.
Should work. Try it.
Tried this and got error.
Attachment 749677
Ok so I got it working but for some reason it will only do one. So like if it hits 25% first then I can't get the others to activate. But if I hit 90% first then I get an email with that percentage. I would like it to act as 4 levels of notification.
I removed the two lines in red and it seems to send anytime you type or do anything.Please Login or Register to view this content.
I added this line in green and it seems to somewhat work but it wants to create them all so if my percentage is 60% i will get two emails created even when I have already done created the 25% last night.
Last edited by jimcclure; 09-30-2021 at 08:47 AM.
Try it this way...
I just tried that and I got no emails to send. It didn't do anything.
Seems like that logic would be earlier instead of in the mail.
something like this. just thinking out loud because I'm not real good at excel macros.Please Login or Register to view this content.
Last edited by jimcclure; 09-30-2021 at 01:27 PM.
Ok I tested that and seems to be working but now I have to figure out how to make it so once it sends an email saying hey 25% on this line. If I put in another PO it wants to send 25% again. Instead it should stop the 25% on that line and only want to send again if it goes about 50% while monitoring the others for the first time they hit 25% too. Does that make sense?
Circling back I believe this is getting close but I need to figure out a way to have it sent one email and not send another one until the next milestone is reached. So once an email is sent for 25% I need to not to continue to send and wait until 50% is reached.
This sort of works but I need it to sent only once and then mark it has sent until the next time.
Last edited by jimcclure; 10-04-2021 at 03:59 PM.
The first time it emails for 25%, enter in another cell on the same row "25emailSent"
then do an if statement if one cell >.25 and another cell <>"25emailSent" then send the email
Please Login or Register to view this content.
I see this but where do I build the other If statement exactly?
Do I need to do like an elseif statement? or build another whole If statement?
Please Login or Register to view this content.
So when the percentage changes it puts the percent in the cell beside it and then in the 2nd cell it puts the percentage email sent. How do I do the sstatement that say check these and then exit if conditions are met?
Last edited by jimcclure; 10-05-2021 at 11:48 AM.
I was thinking more like
Please Login or Register to view this content.
I did this and thought I was on to something but I think I've messed up stillPlease Login or Register to view this content.
Last edited by jimcclure; 10-05-2021 at 03:31 PM.
This works first round but then stops afterward .25 do you see why?
ElseIf should not be required, the "if " statement is all on one line.
You can add the EXP# to the subject, just to show what line is being emailed, for test purposes.Please Login or Register to view this content.
Please Login or Register to view this content.
You are so amazing and I am very thankful for your time and help!!!
Last edited by jimcclure; 10-06-2021 at 10:16 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks