+ Reply to Thread
Results 1 to 20 of 20

If Then Statement Not Working - VBA Excel 2012

  1. #1
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    If Then Statement Not Working - VBA Excel 2012

    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.
    Last edited by eemiller1997; 11-21-2012 at 11:57 AM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: If Then Statement Not Working - VBA Excel 2012

    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?
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: If Then Statement Not Working - VBA Excel 2012

    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!

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: If Then Statement Not Working - VBA Excel 2012

    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.

  5. #5
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: If Then Statement Not Working - VBA Excel 2012

    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?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: If Then Statement Not Working - VBA Excel 2012

    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.

  7. #7
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: If Then Statement Not Working - VBA Excel 2012

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

    Can you upload an example for us to test?
    A macro does what you tell it, not what you want.

  8. #8
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: If Then Statement Not Working - VBA Excel 2012

    Quote Originally Posted by eemiller1997 View Post
    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.

  9. #9
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: If Then Statement Not Working - VBA Excel 2012

    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.

  10. #10
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: If Then Statement Not Working - VBA Excel 2012

    Quote Originally Posted by Norie View Post
    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.

  11. #11
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: If Then Statement Not Working - VBA Excel 2012

    Quote Originally Posted by thinkspac View Post
    Add after the Else Statement:

    Please Login or Register  to view this content.
    Yay! I had gotten it right! Thank you

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: If Then Statement Not Working - VBA Excel 2012

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

  13. #13
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: If Then Statement Not Working - VBA Excel 2012

    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?

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    A checkbox returns a boolean value TRUE/FALSE not text "TRUE"/"FALSE".

    So basically drop the quotes.

  15. #15
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: If Then Statement Not Working - VBA Excel 2012

    Try removing the Quotations around TRUE and FALSE

  16. #16
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: If Then Statement Not Working - VBA Excel 2012

    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!

  17. #17
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: If Then Statement Not Working - VBA Excel 2012

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

  18. #18
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: If Then Statement Not Working - VBA Excel 2012

    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!

  19. #19
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: If Then Statement Not Working - VBA Excel 2012

    Don't forget to mark the thread as solved.

  20. #20
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: If Then Statement Not Working - VBA Excel 2012

    How do I do that?

    Never mind - found it

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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