# If one cell contains #, & other cell is blank, show message & require blank be resolved

1. ## If one cell contains #, & other cell is blank, show message & require blank be resolved

Thank you in advance to any help that comes my way. I am trying to figure out how to make the following happen:

If B5:B6 contains numbers, and C5 is blank, then show messagebox that says "You must select a code for your time."
If B7:B8 contains numbers, and C7 is blank, then show messagebox that says "You must select a code for your time."
If B9:B10 contains numbers, and C9 is blank, then show messagebox that says "You must select a code for your time."
If B11:B12 contains numbers, and C11 is blank, then show messagebox that says "You must select a code for your time."
If B13:B14 contains numbers, and C13 is blank, then show messagebox that says "You must select a code for your time."

If C5:C13 contains any information, and B16 is blank, then show messagebox that says "You have coded a day with no scheduled hours. Please enter the number of hours you were scheduled for the day you have coded."

2. ## Re: If one cell contains a number, but another cell is blank, show a message.

Your tests are not clear whether all the values must contain numbers or any one of them contains numbers so in those cases I assumed that if in any cell of the range contained a number then the message would be shown...

The code in the Worksheet_Change sheet would go something along these lines.... this should get your started.

3. ## Re: If one cell contains a number, but another cell is blank, show a message.

@tkowal...

If the "B" column contains NUMBERS, then the "C" column must contain a label. If the "C" column contains ANYTHING, then B16 must contain a number. (There is a circumstance in which the B column may contain the text "Days Off" and it is okay for the C column to be blank. The lables in column C may contain numbers, letters, or a combination of both.)

I guess I should also have mentioned that I do not want the worksheet to test for these things until a macro button with the code below is clicked. I took the code you suggested and inserted it into my existing macro. With how it is configured below, if column B contains numbers, but column C is blank, it does indeed give you the popup message. However, it also lets you click ok and continue with the macro. So right now it is more of an FYI, but I need for the user to HAVE to enter the needed info into column C before the rest of the macro can be completed.

4. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

Does anyone have any thoughts on how to modify my code to force the user to enter the needed information into column C before the macro proceeds with the remainder of its operation?

5. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

ValidateMe.xlsm

Try this and see if it functionally does what you want?

6. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

At the top of my head, you could when the error is trapped set the cell to a value of an InputBox and then continue on?

This should pause execution until the Input box has been filled.

7. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

@tkowal: That does provide what I need, and functions similar to how my code above does. However, is there a way to make it so the remainder of my code above (which creates PDF, saves it to a network drive, and emails a copy of it, and saves the excel document) cannot function until the errors are cleared out?

Right now it just warns you. I want it to also prevent further action until the errors are gone.

8. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

Perhaps simply exiting before doing your saves and print.....

9. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

Dude...that does exactly what I want it to do. Kudos.

10. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

Actually, I have one more question. What you suggested lastly works well within my existing macro to prevent the user from moving forward with errors. But I had a blank button available, and so I also created a "Test for Errors" button with the original code you suggested. How would I modify it slightly to show a message that says "There are no errors" if no errors are detected?

Finally, Cells in column B should be allowed to have the text "Days Off" without having to enter anything into the corresponding Column C. The code currently gives me the warning if I have "Days Off" but no code in colulmn C. How do I make an exception to this?

11. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

Not having your code in front of me..... something like this?

12. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

That worked. Thanks again.

13. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

The second part of your "Final Question" ....

In each test... where you want the exception to occur add the if statement with UCase(Trim(....
UCase = make all the text Uppercase..some people type all upper or all lower case..
Trim = Strip off any unnecessary spaces...sometimes people add spaces before and after...

Please note that this code is not necessarily the most efficient... there are many other ways of doing what you require! My philosophy is first get it working then dwell on improving it!

14. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

I am getting a "next without for" error.

15. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

Nevermind. Your stuff is perfect, I just didn't have it all in there. I THINK that answers everything lol.

16. ## Re: If one cell contains #, & other cell is blank, show message & require blank be resolve

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1