Hi
I have been trying to add to the below, without much success. What I want to do is generate a message if the value in Q12 is greater than the value of S12. Cell Q12 contains a formula. I have to do this on a couple more cells which is why i am unable to use worksheet calculate. i just have not been able to work out how to add it in.
Please Login or Register to view this content.
First, you may be overthinking it. To add that check to your existing code you can add:
But there are problems with this because Q12 has a formula. If Q12 uses values that are not on this worksheet, then the Change event won't happen. It only happens when the cell content changes, not a formula result. I am not clear on why you cannot use Worksheet_Calculate.Please Login or Register to view this content.
I suspect there may be more information we need to really understand this problem, such as what event will cause you to want to reevaluate whether Q2 > S12. Any time either one of them changes? Or when something else changes?
Hi Jeff,
Thanks for your reply.
You are right I am probably trying to overcomplicate the issue.
Thankyou for pointing out the change event will only trigger if the calculation is on the same worksheet, which does make sense seeing as it is a worksheet change! So with this in mind I have moved the calculations so they are on the same sheet, what I now need to do is only have the message box appear if that particular cell changes, which is why I believe I cannot use the worksheet calculate as I have more than one?
Please Login or Register to view this content.
Bump - Is anyone able to offer some help, would be much appreciated.
Hi michelle 1,
Please try;
Please Login or Register to view this content.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
"...what I now need to do is only have the message box appear if that particular cell changes, which is why I believe I cannot use the worksheet calculate as I have more than one?" I am sorry but I can't make sense out of that sentence.
You want to determine if any of those inequalities is true whenever a value on that sheet changes. You now have code that will do that. Does your code not work?
If you need more help I am going to suggest attaching your file and explain what cells . It is sometimes very difficult to solve a problem just looking at a little bit of code without being able to see the context of what you are doing.
Dear both thanks for helping me with this.
I have attached the file.
The code does work but I am receiving the message when other cells are updated. i.e if you change E12 I receive message 1 and message 2 (message 1 is only related to cell I7)
hope that makes sense.
Hi michelle 1,
I must admit that I am tired and a bit confused;(
Does this work for you?
Regards.Please Login or Register to view this content.
Hi Winon,
Sorry to confuse you, hopefully the below makes sense?
I have added the code into the worksheet and "message 1" appears if E7 changes and is less than I7, which
is correct. The problem is if I type in T7 then I get message 1 appear again. I would like "message 1" only to appear based on cell E7, "Message 2" only based on data in E12 and "Message 3" only on Q12.
Really appreciate your help and guidance.
Please Login or Register to view this content.
Hello michelle 1,
You should not be able to enter data into Cells with formulas, like T7, E14 and S12, and I have prevented changes to those Cells., amongst some other Validation Settings.
Is the attached sample Workbook now closer to your requirements?
Regards.
Hi Winon,
It is closer but how can I stop message 1 showing up when changes are made in cell E12? I do not want to see message 1 again when I click other cells.
Cell i7 message 1
Cell E12 message 2
Also you changed the cell reference from i7 to E7, think that was in error?
Sorry for confusing you.
Please Login or Register to view this content.
Hi michelle 1,
Thank you for the feedback, and sorry for the mistake.
For as long incorrect Data appears in a Cell the message box relating to that Cell will keep on popping up. In order for the correct data to be entered, I have added an undo to force the user to only enter valid data, until that specific entry is validated as correct and the relevant message box "can take a tea break".
Please check the Workbook now.
Regards.
Yes that is it, Winon you are amazing!
I cannot thank you enough for your help, perseverance and patience.
thank you so much.
You are welcome michelle 1, Glad I could help
Thank you for the "Rep".
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Kind Regards.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks