I have a grid of cells that evaluate to "yes" (true) or "no" (false)
Upon entering a name in a particular range of cells, J7:J165, if a particular condition evaluates to true, I would like a message box to appear, warning of the error, and then clear the name out of the cell and keep the focus on it.
It would look something like:
a name gets picked from a drop down menu in cell J7
One of the conditions would be in cell J172. If it's "no", nothing happens, if it's true, then how I currently have it set up is that it calls a public function, for instance, availabilitycheck(),....the formula in cell J172 is:
=IF(OR(Schedule!B7<VLOOKUP(J7,EmpInfo!$C$3:$V$200,7,FALSE),((IF(F7="close",(MOD($AE$4-B7,1))*24,(MOD(F7-B7,1))*24))>=MOD(VLOOKUP(J7,EmpInfo!$C$3:$V$200,8,FALSE)-Schedule!B7,1)*24)),availability(),"no")
The function contains the message box, and it is here I would like to also be able to clear the contents of the selected cell.
I had a similar question not too long ago, and the solution was:
I also experimented with this, not as a public function, obviously......
I tried substituting Vlookup in place of the worksheetfunction.countif, and taking out the function call in the excel vlookup formula and replacing with "yes" (as the vlookup formula would evaluate to "yes" or "no", but I could not get the vba code to work.
In the vb code, to locate the cell in question that would potentially throw up the messagebox, the vlookup expression would take the value in the cell that the name was dropped in, then check it against the range B172:Q330, the cell that contains the true/false value would be 9 columns over.....the range is greater than 9 columns because I would just use this a constant range, just change the # of columns where necessary.
I thought maybe the fact that the "cell" that the name gets dropped in was actually a collection of merged cells was the culprit, but the code I included here from a previous response works on the same range, and does exactly what I want to do in this situation.
I explain things horribly, so +rep if you even understand what I just wrote......
Bookmarks