Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing Textbox Values in Userform (VBA/Word/97 on)

    Continuing on from original <post#=247933>post 247933</post#> : Via a userform I have created a number of textboxes that users type percentage values into (these values are then summed, on the assumption that they will only be numbers). Thanks to HansV this part is working very well.

    Now users would like the option to display '<1' in those textboxes where the value is less than one percent but I am unsure how to let the code know that the values that are not text that they must be ignored in the final count. At present, whilst the user is filling in entries, there is a display of the current count on the userform. If they exceed 100% a dialog box announces the fact.

    To simplify data entry, I have placed an option button adjacent to the textbox and when chosen, the text '<1' appears where a number would have gone. The attachment shows the three possible states.
    1. <1 and the option button selected.
    2. 14 and the option button not selected.
    3. 00 (no value entered) and the option button not selected.

    I am not clear on how to sum values when some textboxes can contain text (how to ignore those textbox values that are not numbers).

    Any pointers will be much appreciated.

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Summing Textbox Values in Userform (VBA/Word/97 on)

    Leigh,

    You can use IsNumeric to test for the presence of non-numeric characters.
    If you can always be assured that the only non-numeric character would be the opening "<", then you could use:

    <pre>Dim strOriginal As String
    Dim strValidated As String

    strOriginal = "<1" 'assigning for testing purposes

    If Not IsNumeric(Left$(strOriginal, 1)) Then
    strValidated = Right$(strOriginal, (Len(strOriginal) - 1))
    Else
    strValidated = strOriginal
    End If
    </pre>

    Otherwise you may need to loop through all of the characters in the textbox to strip out the non-numeric ones.

    Gary

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Summing Textbox Values in Userform (VBA/Word/97 on)

    This is an example where sloppy coding is actually an advantage.

    The Val function will return zero for strings that are not numeric. So:

    Dim MyString as String
    Dim MyInteger as Integer
    Dim MyTotal as Long

    MyString="<1"

    MyInteger(MyString) will return zero.
    MyTotal=MyTotal + MyInteger

    As I say, this is sloppy coding. Validation is always better but in this case the oddity satisfies the condition required.

    Correctly we should code this as for the same result.

    Dim MyString as String
    Dim MyInteger as Integer
    Dim MyTotal as Long

    MyString="<1"

    If IsNumeric(MyString) then
    MyInteger = Val(MyString)
    MyTotal=MyTotal + MyInteger
    Endif

    Regards,

    Kevin Bell

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

    Re: Summing Textbox Values in Userform (VBA/Word/97 on)

    Kevin,

    It isn't sloppy coding if you use it with intent. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

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