If Then Statement Not Working - VBA Excel 2012

[eemiller1997]

Following the example I found as follows:

Please Login or Register  to view this content.

I modified it to my needs, but I think I missed something.

I have a drop-down box in my UserForm that allows for an answer of Yes or No. Basically, I am trying to add an If, Then statement that would do the following:

If the

1. answer is No, then on the worksheet in cells N37 and N52 will have ***** entered. This will let the user know later not to worry about those two steps.
2. If the answer is No, then on the worksheet in cell I37 the following will be input, "No Travel Arrangements Necessary"
3. If the answers is Yes, (Else) then on the worksheet in cell I37 the following will be input, "Travel/Hotel Authorization Made"

The name of the UserForm Value is Cand1_ReloNeeded. The options for answers are Yes and No.

I had used the following code in an attempt to code that:

Please Login or Register  to view this content.

It didn't work. Where did I go wrong?

I appreciate your help.

[Norie]

It should be Range("N37"), Range("N52") etc.

Also the If needs fixing.

Please Login or Register  to view this content.

By the way, why are you apparently storing the data like this? Wouldn't some sort of tabular format be better?

[eemiller1997]

Thank you for your help!

I have copied and pasted that code and when I ran it to test it, I selected Yes, and recieved a "Compile Error: Block If without End If" error. When I looked into this I discovered it required an "End If", so I added it to the end, as follows:

Please Login or Register  to view this content.

I didn't get the error after that; however, when I selected "no" I didn't ***** in cell N37. I did in N52 and I did get the "No Travel Necessary in I37. Just no **** in cell N37.

The reason for the input in this manner instead of a tabular format is that we must be able to print the final excel worksheet out so the users can continue with the process of scheduling and completing a series of steps for each candidate to be interviewed. The final output that will be printed is something that looks like a form.

I appreciate your help! Thank you so much!

[Norie]

If it worked for N52 and I37 it should work for N37.

By the way, what other values can go in this textbox?

If it's just yes/no perhaps it would be better to use a checkbox.

[eemiller1997]

In addition to the above question, I also just tested the Yes answer. Everything worked, except - it didn't clear the **** from N52. This is interesting as the formatting doesn't consider the fact the user may realize later they must change their answer while still inputing data using the UserForm. Sure, they could always simply delete that from the excel worksheet, but the idea is they wouldn't have to. Any ideas?

[Norie]

You haven't set the code to clear N52 if the value is Yes.

By the way, my original code does appear to have an End If.

[thinkspac]

Norie's code is correct, it should be placing the information in the correct spots.

Can you upload an example for us to test?

[thinkspac]

In addition to the above question, I also just tested the Yes answer. Everything worked, except - it didn't clear the **** from N52. This is interesting as the formatting doesn't consider the fact the user may realize later they must change their answer while still inputing data using the UserForm. Sure, they could always simply delete that from the excel worksheet, but the idea is they wouldn't have to. Any ideas?

Add after the Else Statement:

Please Login or Register  to view this content.

[eemiller1997]

You are right. It must be too early for me on a Monday morning after having had a four day weekend So, I must not have copied the whole thing. Ugh! Well, I got it figured out

Yes, it does appear the code is correct, that is the crazy thing. It must have something to do with merged cells. Yes, attached is the example...

Multi-Page Trial Run.xlsm

How would the code read to clear it, like this?

Please Login or Register  to view this content.

[eemiller1997]

By the way, what other values can go in this textbox?

If it's just yes/no perhaps it would be better to use a checkbox.

Yes, this is just a yes or no, so a checkbox could be used.

[eemiller1997]

Add after the Else Statement:

Please Login or Register  to view this content.

Yay! I had gotten it right! Thank you

[Norie]

A checkbox is a better than a texbox, the user could enter anything in a textbox.

[eemiller1997]

I have changed it to a checkbox with one for Yes and one for No. Under the one for Yes, I put the following code:

Please Login or Register  to view this content.

What is happening is when I check the checkbox for Yes, then I'm getting the "Else" options. When "Yes" isn't checked, I'm not getting anything.

If I change the code to read

Please Login or Register  to view this content.

Then, the same thing happens. I'm not getting my desired results. What am I doing wrong?

[Norie]

A checkbox returns a boolean value TRUE/FALSE not text "TRUE"/"FALSE".

So basically drop the quotes.

[thinkspac]

Try removing the Quotations around TRUE and FALSE

[eemiller1997]

That worked beautifully, thank you!

An interesting note, still needing to address.... Cell N37 is still not showing ***** as set up in the code. However, I did notice when I have my curser in that cell the formula bar does show *****, however, it's not actually shown in the actual cell. Any ideas?

Thank you again for your help!

[thinkspac]

Do you have any conditional formatting that is triggering a Font Color Change, or perhaps the font color was changed elsewhere?

[eemiller1997]

No, I didn't have any conditional formatting triggering any font color change anywhere. But, you are right. The color wasn't correct. Sometimes the simplist thing can stump everything. That fixed it. Thank you!

[thinkspac]

Don't forget to mark the thread as solved.

[eemiller1997]

How do I do that?

Never mind - found it