Automatically running a macro based on value of cell (generated through IF statement)

djdan

New Member
Joined
Apr 7, 2011
Messages
2
Hi

I have spent all day (9:00am - 5:00pm!) working on this simple problem.. but no solution as yet. I've been looking around various forums for similar issues, copying and pasting code etc... but I still can't get it.

I have a spreadsheet (Excel 2007). I have listed out a couple of questions, and to answer, the user will need to select either "yes" or "no" from a drop down list. These answers are all given in column H.

I have used an if statement to turn the "yes" or "no" into a number, so I can count the 'yes' answers. So for example, a corresponding cell in Column K will say =IF(H18="yes","1","0"). I then total these up (again, in column K). Back to column H, there is a cell that in effect becomes a trigger, and it is dependent on the total value from column K.

For Example
There are 3 questions. Once the user has selected "yes" to each question (giving a corresponding number 1 in the K column), the total [K22] will reach "3" (3 x "yes" answers).

Once the total reaches "3" then cell H24 will say "YES" (therefore telling the user that further action is required) due to the formula =IF(K22=3,"YES","No").

Further, I have typed YES in H23. The way that the spreadsheet is set up, when H23 and H24 both say "YES"... then action needs to be taken. So again, through if statements, cell H26 becomes activated automatically and also states "YES".

All of the above works perfectly (after all, it's only simple if statements...). However, once H26 becomes activated, I want an email to be sent through outlook, so I have set up a macro to send the email. Again, that works fine in its current basic form.

The real problem I'm having is how to automatically trigger the email macro.

I spent ages looking at "Worksheet_Change" macro's which work fine if you manually type a value into a cell and click enter. However, because my spreadsheet is updated automatically (through adding up the number of cells with "yes"), the user will not actually type in the cell and press enter.

I then tried "Worksheet_Calculate" because I read that this is required where the cell value changes due to formula. Aha! When H26 changed to "yes", the email was sent automatically. Problem was, any other change to the spreadsheet eg. entering data into A1, would repeat the macro... so I got email after email after email.

After a long time there, I thought... right, I'll get myself a 'button' to launch the macro. So the user will click on the button! Simple! The problem is, I only want the button to appear when H26 says "yes".

In other words:
Because H23 and H24 BOTH say "yes"... H26 automatically says "Yes" (meaning action is required).
Therefore, Button automatically appears.

However, I cannot get this to work!

Again, I've tried the hide/appear button when I manually type in the box, and the button will appear or disappear no problem. But I cannot get it to do the same based on the value of a cell that results from an IF statement.

Please can someone guide me through this? In my head it is so simple.. but I can't for the life of me get it to work in practice.

In summary:
Dependent on other cell values, and through various IF statements, cell H26 will either say "yes" or "no". When it says "yes", the user is required to promptly take action. To ensure that action is followed up on, I want an email to be sent automatically to the user.

I have the spreadsheet set up (albeit in basic form) so that H26 will change to "yes" no problem. I have also set up the macro so that the email gets sent (if you manually run the macro).

What I'm stuck on... is linking the email macro (named "Macro1") to cell H26 so that it is all done in one automatic motion.

I'll stop there, because i'm making it sound more complicated than it is.

Grateful for any help you can offer.. just desperately want to solve this now.

Thank you

Cheers
Dan
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

arkusM

Well-known Member
Joined
Apr 12, 2007
Messages
560
Dan,

Have you concidered using an Application.OnTime and running it every few seconds 5 or so?
I helped a guy a while back monitor some stocks that where updating remoting and therefor not triggering an event. So We added an OnTime to check the monitred cells agaisnta a set of data that was copyed in an adjecent cell. If they matched nothing, if they were different it would trigger another Macro. If something changed or nothing changed the macro would copy the current data (being updated with external data) on the cells for comparison. Makes sense?

I think this would work for you.
If you want I could try to digg up the code for you to try and adapt.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top