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

Topic

New Reply

Continuing on from original post 247933 : 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.

Reply | Quote

Replies

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:

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

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

Gary

Reply | Quote

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

Reply | Quote

Kevin,

It isn’t sloppy coding if you use it with intent.

Reply | Quote