Results 1 to 9 of 9

Thread: Force certain Values in Input Box

  1. #1
    Board Regular
    Join Date
    Jun 2006
    Posts
    5,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Force certain Values in Input Box

    I have the following code below

    Sub Input_Data()

    Code:
     Sub Input_Data ()
     Dim myValue As Variant
    myValue = InputBox("Enter The net income amount")
    Range("B3").Value = myValue
    myValue = InputBox("Enter Tax rate")
    Range("F4").Value = myValue
    End Sub


    I would like the following amended

    Range("B3").Value = myValue -this must force the user to only enter a whole number for eg 5000, 6500 etc

    Range("F4").Value = myValue -this must force the user to enter a % for eg 30%, 45% etc


    Your assistance in this regard is most appreciated
    Last edited by howard; Aug 1st, 2019 at 10:00 PM.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,212
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Force certain Values in Input Box

    See if this code does what you want...
    Code:
    Sub Input_Data() Dim myValue As Variant Do myValue = InputBox("Enter The net income amount") If myValue Like "*[!0-9]*" Then MsgBox "Whole numbers only!" Loop While myValue Like "*[!0-9]*" Range("B3").Value = myValue Do myValue = InputBox("Enter Tax rate") If Right(myValue, 1) <> "%" Or myValue Like "*[!0-9.%]*" Or myValue Like "*.*.*" Then MsgBox "That is not a valid percentage!" Loop While Right(myValue, 1) <> "%" Or myValue Like "*[!0-9.%]*" Or myValue Like "*.*.*" Range("F4").Value = myValue End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,187
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Force certain Values in Input Box

    something like this

    Code:
    Sub Input_Data()
        Dim myValue As Variant
        Do While True
            myValue = InputBox("Enter The net income amount")
            If myValue = "" Then
                Exit Sub
            Else
                If Not IsNumeric(myValue) Then
                    MsgBox "Enter number"
                ElseIf myValue - Int(myValue) <> 0 Then
                    MsgBox "Enter a whole number"
                ElseIf myValue <= 0 Then
                    MsgBox "Enter positive value"
                Else
                    Exit Do
                End If
            End If
        Loop
        Range("B3").Value = myValue
    End Sub
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Jun 2006
    Posts
    5,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Force certain Values in Input Box

    Thanks Rick. Code works perfectly

  5. #5
    Board Regular
    Join Date
    Jun 2006
    Posts
    5,334
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Force certain Values in Input Box

    Thanks for the help, Dante

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,187
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Force certain Values in Input Box

    Quote Originally Posted by howard View Post
    Thanks for the help, Dante
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Force certain Values in Input Box

    Quote Originally Posted by Rick Rothstein View Post
    See if this code does what you want...
    Code:
    If myValue Like "*[!0-9]*" Then...


    I hate to admit it, but i have not seen that one before. Impressive.

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,212
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Force certain Values in Input Box

    Quote Originally Posted by Steve_ View Post
    I hate to admit it, but i have not seen that one before. Impressive.
    The Like operator allows for some very powerful pattern testing... nowhere near what Regular Expressions can do, of course, but still quite powerful in its own right. That particular pattern is looking for any number of character on either side of a non-digit (everything in between square brackets represents a single character). If you look up the Like operator in the help files, it will give you the full story as to what kind of patterns you can use it with to match text.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Force certain Values in Input Box

    Quote Originally Posted by Rick Rothstein View Post
    The Like operator allows for some very powerful pattern testing... nowhere near what Regular Expressions can do, of course, but still quite powerful in its own right. That particular pattern is looking for any number of character on either side of a non-digit (everything in between square brackets represents a single character). If you look up the Like operator in the help files, it will give you the full story as to what kind of patterns you can use it with to match text.

    WAY ahead of you. Reading all about it now. Cannot believe I have coded VBA for this many flippin years and never ran into the Like operator.


    I have been using inStr inside of do/loops to filter out unwanted characters for years. Thats over.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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