Clear Cell After Messagebox

[ZooTV92]

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:

Please Login or Register  to view this content.

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

[TMS]

Please post a sample workbook so the code and data can be tested in context.

Regards

[ZooTV92]

attached.....thank you!

[TMS]

Apologies: I did look at the workbook. I can see how the current code works but, sadly, I don't really understand what you want to do differently.

I'll see if I can get someone else to have a look.

Regards

[JBeaucaire]

your current code seems to check the current column for duplicates. What is the purpose of the "yes" in the rows below?

[ZooTV92]

The grid at the bottom checks each name against certain conditions. If a condition comes up as a "yes", it pops a message box, which is easy enough. How I would like it to work is that upon clicking "yes" in the messagebox, the contents of the cell that the name was dropped in should be cleared, and the focus remain on that cell. I cannot get that part to work.

The formula that checks for duplicates works exactly the way I want the rest of it to work. If a name that gets dropped is a duplicate, then it pops the messagebox, and the name is cleared out of the cell.

Currently, J172 is the only cell I have experimented with that calls a public function if the formula evaluates to true. The function is located in module 4. I have also toyed around with using Vlookup in the same code that the duplicate check is set up in. I cannot get any combination to clear the contents of the cell after the message box is acknowledged.

[JBeaucaire]

1) Change the named range EmpList Refers To: formula to this:

=OFFSET(EmpInfo!$C$3,,,COUNTA(EmpInfo!$A:$A)-1,1)

2) Simplified some EmpInfo formulas

C3: =B3&", "&A3
E3: =DATEDIF(D3,TODAY(),"y")

Also, the ADD EMPLOYEE macro now adds these formulas properly including cleaned up phone numbers.

3) Schedule:

- drop down list will not show employees until you fill in a shift
- A6 is a drop down for jobs, used later to check the job certifications
- Not sure any of the stuff at the bottom is needed anymore
- This is the new sheet code:

Please Login or Register  to view this content.

4)

[ZooTV92]

That is going waaaaayyyyy above and beyond.

Your invested time is greatly appreciated. Hopefully I can make enough sense out of it to make the rest of the conditions work

Regards

[JBeaucaire]

If the conditions are more jobs, just add them as shown and the existing code will check for those jobs, too.

If it's new kinds of conditions, the samples I gave for checking TimeOFF, Availability and Job Certifications by job name should provide a basis for developing new tests.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[ZooTV92]

Just for S's and giggles, is there a way to do what I was looking for in the beginning, and that is when a specific cell turns to "yes", it launches a messagebox warning of an error, and then clears the contents of the cell that the name was dropped in (merged cells - and I can't change that) while keeping the focus on that cell?

To be honest, your code, while impressive, is a little beyond my understanding, and for me to look forward, I need to be able to understand what I'm looking at.

The assistance of this board is and always will be appreciated.

[JBeaucaire]

We're all about teaching! Hehe, streeeeeeeeeetch....

Here's the code again with more guidelines about what each line does, let's look through the code and find what isn't logical to you yet. Use F8 to activate the macro and step through the code one line at a time, after each code executes, hover your mouse over the variables to see the results, and check the sheets to see how it got those answers.

Please Login or Register  to view this content.

Meanwhile, a simple way to check if a cell cell "yes" in a specific distance away/below:

Please Login or Register  to view this content.