+ Reply to Thread
Results 1 to 9 of 9

Excel Userform cannot perform multiple Ifs and Elses.

  1. #1
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    17

    Excel Userform cannot perform multiple Ifs and Elses.

    I hope someone will solve these problems for me. I'm using Excel in Office 365.

    In the userform I have some codes and a few of them need some conditions to display the results.
    However, when I used multiple Ifs and Else in the coding, something went wrong. It seems that the code will run according to which comes first.

    It is very difficult to explain here so I've attached 2 files: Sample1 and Sample2.

    Sample1 is the problematic one. I kept getting wrong message that does not 'match' the condition.
    Sample2 has all the problematic codes "removed" (use apostrophes) to inactive them.
    It runs smoothly, but I've no control over what should appear in the fields that have certain conditions.

    Please run the 2 files and you can see what I meant.

    Thank you very much in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,072

    Re: Excel Userform cannot perform multiple Ifs and Elses.

    Quote Originally Posted by KSChan View Post
    Sample1 is the problematic one. I kept getting wrong message that does not 'match' the condition.
    There is nothing intrinsically wrong with your code. Your problem seems to be that it does not do what you expect. However, I don't know what you expect.

    Please describe the scenario where you think it fails, and what you want to happen instead under those conditions.
    Jeff

    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    If someone helped you, please click on the star icon at the bottom of their post
    If your problem is solved, please go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,072

    Re: Excel Userform cannot perform multiple Ifs and Elses.

    I do see a problem in your logic.

    When you combine And and Or in a single condition, the And takes precedence. This line of code
    Please Login or Register  to view this content.
    means this:
    Please Login or Register  to view this content.
    So if your value is under 100, this If will always be TRUE, regardless of whether the units are feet or cm.

    You need to put parentheses around the Or to force it to be evaluated like this:
    Please Login or Register  to view this content.
    Similarly this should be
    Please Login or Register  to view this content.
    The same problem occurs in the second block of code that you commented out.

  4. #4
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,003

    Re: Excel Userform cannot perform multiple Ifs and Elses.

    here the control happen by exit of textbox


    Kind regards
    Leo
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Excel Userform cannot perform multiple Ifs and Elses.

    Thanks to 6StringJazzer for solving the If & Else problems for the height and weight. It is working as I expected. Thank you. I appreciate your help.

    Thanks to LeoTaxi also for showing me another way to approach the If & Else problems.

    But I encounter another problem now which I hope LeoTaxi can help: the warning message for me to fill in the name field when I want to close the userform. My original was to warn that the name is not keyed in when trying to save the data.
    But now that you have made the message appear once the name field is ignored/blank, the userform will close only if I keyed in all the fields. On opening the userform and if I decided not to use it at all, say I want to view the data first, I couldn't close the userform because it keeps asking me to fill in the name field. I've to end task from file manager if I want to.
    Is there a way to solve it? Thanks.

  6. #6
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Excel Userform cannot perform multiple Ifs and Elses.

    I must apolozise to Leo for mentioning that he made the message appear once the name field is ignored or is blank.
    It was I who is trying to have the message appear once the name field is ignored/blank. So I used this:
    Please Login or Register  to view this content.
    So as I mentioned earlier, it wouldn't allow me to close the userform if I do not want to use the form. I've to complete every fields and save before I could close it.
    Any way I can solve this? Thanks.
    Last edited by 6StringJazzer; 05-14-2018 at 10:26 AM. Reason: code tags

  7. #7
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,003

    Re: Excel Userform cannot perform multiple Ifs and Elses.

    Something like this for exit Reg2

    Please Login or Register  to view this content.
    Kind regards
    Leo

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    2013, 2016
    Posts
    12,072

    Re: Excel Userform cannot perform multiple Ifs and Elses.

    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time in post #6 to keep the thread moving, but please add them yourself next time. --6StringJazzer

  9. #9
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Excel Userform cannot perform multiple Ifs and Elses.

    Thanks 6StringJazzer, for the reminder to use code tags.
    Thanks Leo, for your new coding. It works now. The userform can close now without me having to type in all the fields.
    Chan

+ 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