Results 1 to 13 of 13

Thread: Numbers only

  1. #1
    JustCallMeAl
    Guest

    Numbers only

    Stumped.

    I would like a VBA (Word 97) textbox to only accept numbers. Been playing around, but to no avail.

    Thanks.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Numbers only

    Try something like the following :<pre>Private Sub TextBox1_Change()
    If Not IsNumeric(TextBox1) And Len(TextBox1) > 0 Then
    TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
    End If
    End Sub</pre>

    If you double click on the the text box in design mode you should be able to access the TextBox1_Change event and enter that code.

    Andrew

  3. #3
    JustCallMeAl
    Guest

    Re: Numbers only

    Thanks. Didn't even know about IsNumeric! <img src=/S/shocked.gif border=0 alt=shocked width=15 height=15>

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers only

    The better way to do this is in the KeyPress event:

    <pre>Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii < 48 Or KeyAscii >57 Then
    Beep
    KeyAscii = 0
    End If
    End Sub
    </pre>


    KeyAscii is the ascii value of the character you typed into the textbox. You can find the ascii value of any character by typing: ? Asc("0")
    in the immediate window of the VBA environment, and hitting the Enter key. Substitute any digit inside the quotes.

    The beauty of this method is that the keystroke is evaluated BEFORE it's added to the text. Setting KeyAscii to 0 cancels the keystroke.

    Feel free to e-mail questions.

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers only

    Nice little bit. I like it. Into the tool kit it goes...
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    JustCallMeAl
    Guest

    Re: Numbers only

    That is what I was originally trying, only I didn't have the KeyAscii = 0 part! Which made it not work.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers only

    I guess this would need some tweaking if you were going to allow for negative numbers, decimal points, or commas in numbers.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    JustCallMeAl
    Guest

    Re: Numbers only

    In my case (since I was the one who originally asked), I needed it to be pure, unadulterated, 100%, homogenized numbers! Nothing more, nothing less. Because I was taking that value and using it in math (all positive) equations. Therefore, either solution was perfectly OK.

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers only

    Ok, in that case:
    <pre>Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    Case 45, 46, 48 To 57 'Minus, Period, 0 - 9
    Case Else
    Beep
    KeyAscii = 0
    End Select
    End Sub
    </pre>


    This way, you can add any ascii value to the Case statment, to make it more versatile.

    I'd discourage you from using comma, because unlike Minus and Period, it doesn't belong in a *real* number, and you'd have problems decoding the resulting text value.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers only

    Ron,

    Thanks for that.

    As JustCallMeAl rightly suggests, if the original problem was solved, then this is all hypothetical. But, as this is a real world problem which I have frequently had to deal with, I would like to take it one more step, just in case it's of use to anyone- and hopefully, to get feedback in the way others have dealt with the problem.

    It can apply to any control where formsatting is to be applied or where input characters are limited- dates particularly come to mind.

    If this was something you had to do often, then there's controls available which allow you to specify any edit mask without having to do any programming. In a big shop where time is important, and where there are some funds available, I'd certainly suggest this is a good way to go.

    In this particular case, not having enough money to buy a third party solution, I'd expand on Ron's contribution to include Andrew's suggestion:

    <pre>Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    ' Edit out the obvious
    Select Case KeyAscii
    Case 45, 46, 48 To 57 'Minus, Period, 0 - 9
    Case Else
    Beep
    KeyAscii = 0
    End Select
    If Not IsNumeric(TextBox1) And Len(TextBox1) > 0 Then
    ' If all the characters are correct, ensure the final
    ' number is OK
    Beep
    KeyAscii = 0
    End If
    End Sub</pre>


    By performing the code at keypress rather than at the change event, it's easier to keep the entry textbox intact (eg, if I'd backspaced to character 2 out of 5 characters, I'm not sure if Andrew's code would have done the right thing. I may have had to do things like keep the previous value, and that would have made the code more complicated).

    Once you've got code which performs well for you after rigourous testing you have something which you can use for anything afterward.

    Yes, the original problem was solved very quickly. That's one thing which happens frequently on the board. But something else which happens is that issues raised, also raise other issues. The resulting solutions can help many who read what's happening, or who come after.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numbers only

    Glad you found a solution that fits, Al. The great thing about the Lounge is that the next post might have an even better solution or one with broader applicability, so stay tuned! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Numbers only

    Nice amalgamation Geoff.

    Just for the record, backspacing is no problem in the TextBox1_Change event, in that it behaves as I have come to expect, the Backspace key (that's the one with tht left pointing arrow above the return key )deletes the character to the left. The left cursor key allows you to go back and enter a number between two others. I'm not sure that I know what the 'right thing' is.

    Also I have never had problems with commas.

    Regards and thanks

    Andrew

  13. #13
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbers only

    Andrew,

    I'm running Excel 2000.

    With your code, if you enter a 5 digit number, press the back arrow three times, and press a letter twice, it clears the rest of the number. I suspect you would need to use something linke Textbox1.Selstart to make it work.

    But my code allows a number with two embedded decimal points. It also does not allow you to enter any numbers after a minus. So it needs work too- for which I don't have the time just now. So I'd be tempted now to buy a solution!
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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