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
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
