+ Reply to Thread
Results 1 to 6 of 6

How to validate Fractional number in userform textbox?

  1. #1
    Registered User
    Join Date
    10-08-2014
    Location
    Asti
    MS-Off Ver
    2010
    Posts
    3

    Question How to validate Fractional number in userform textbox?

    Hi!

    I have a userform that should accept only fractional values, such as 7/21 , 1-7/8 and so on.
    What is the best way to implement such validation and how?

    For numerical values there's a method IsNumeric, however, is there anything I can use for Fractional numbers?

    I tried LIKE method but it didn't work for me even if I know it wouldn't accept something similar to 1-7/8...
    Please Login or Register  to view this content.
    And I need to use it in Change event, so that user can input only allowed characters, such as "-" and "/" and digits.

    Thank you in advance!

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,322

    Re: How to validate Fractional number in userform textbox?

    "...accept only fractional values..."

    Please Login or Register  to view this content.
    Last edited by protonLeah; 03-27-2021 at 02:32 PM.
    Ben Van Johnson

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,726

    Re: How to validate Fractional number in userform textbox?

    This is difficult to use in a Change event because a user may start typing the beginning of a valid fractional expression, but if they don't finish then it's not valid. So you have to check twice--once when they are typing, and once when they are ready to submit.

    The best way to deal with this kind of validation is regular expressions. I have shown one validation that can be used as the user types into the text box, and another one that must be used when they are finished, typically by hitting a Submit button.

    Valid forms for fractions in progress are:

    #

    #-

    #-#

    #-#/

    #-#/#

    #/

    #/#

    where # is 1 or more digits


    Valid forms for completed fractions are:

    #/#

    #-#/#

    where # is 1 or more digits



    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,764

    Re: How to validate Fractional number in userform textbox?

    This should work using the Like operator repeatedly (note I assign the TextBox value to the String variable S because it makes the If..Then statement shorter)...
    Please Login or Register  to view this content.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,726

    Re: How to validate Fractional number in userform textbox?

    Quote Originally Posted by zaurb View Post
    I need to use it in Change event
    Quote Originally Posted by Rick Rothstein View Post
    This should work
    This method caused rejections as I typed "1-7/8" as in the OP. It rejected it as I typed each successive character, even after I just typed "1", until I completed the entire expression. The condition

    Not S Like "*#/#*"

    requires a slash in all expressions, but an unfinished expression will not have a slash. I get errors on each invocation of the Change event as I type "1-7".

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,764

    Re: How to validate Fractional number in userform textbox?

    Quote Originally Posted by 6StringJazzer View Post
    This method caused rejections as I typed "1-7/8" as in the OP. It rejected it as I typed each successive character,
    Argh! I missed that the OP wanted to do this in the Change event character-by-character... my code was for approving the entry after all characters were typed in. I'm not exactly sure how you could validate this type of entry in a Change event. What if the user typed "1-" (without the quotes) and stopped? The entry is not valid as a final value but in a change event it would be allowed with the anticipation that a number would be typed next. Maybe the OP needs to tell us how his overall entry mechanism is supposed to work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to Number each Userform entry in a textbox 1 -10
    By jdcortez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2018, 02:08 AM
  2. userform testbox to create number of textbox to another userform
    By markusvirus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-10-2017, 07:48 AM
  3. Userform Textbox Number Formatting VBA
    By chriswathen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-01-2016, 02:37 PM
  4. [SOLVED] Add 1 to the last number in a textbox in a userform
    By skip2mylew in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2014, 07:45 AM
  5. how to generate serial number on a textbox in userform
    By minionrush in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2013, 05:12 AM
  6. Fractional format to userform label
    By adam2308 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2012, 05:45 PM
  7. [SOLVED] Userform textbox number formats
    By PhilM in forum Excel General
    Replies: 2
    Last Post: 06-07-2006, 06:15 AM

Tags for this Thread

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