MrExcel Publishing
Your One Stop for Excel Tips & Solutions

number format in form textboes

Posted by anna daly on November 09, 2000 1:30 PM

hi all,

I have a form that contains several textboxes, some of which are for user imput and others are for output display. Does anybody know how I can limit the entery into the textboes as integers, and how to display them with commas between every 3 significant figure?

In my VBA code i have

textbox1.value = textbox2.value / textbox3.value

I would like to limit the input to textboxes 2 and 3 as whole numbers. Please please help!!


Anna Daly

Posted by Tim Francis-Wright on November 09, 2000 3:10 PM

to format a textbox, use the following in your code:-

textbox2.value = Format(textbox2.Value, "#,##0;(#,##0)")

to prevent entries that are not up to your standards, use the following procedures in
the code of the userform:-
[You can combine the two, but if you need
to check more than one textbox, it's easier
to have a bunch of short beforeupdate events
that call a general subroutine]

Private Sub textbox1_BeforeUpdate(ByVal TryAgain As MSForms.ReturnBoolean)
If CheckNum(textbox1) = False Then TryAgain = True
End Sub

Function CheckNum(TB As Control)
If Not (IsNumeric(TB.Value)) Then
MsgBox "That requires a numeric entry. Please try again.", vbExclamation
CheckNum = False
Elseif TB.Value <> Int(TB.Value) then
MsgBox "Please enter a whole number.", vbExclamation
Checknum = False
CheckNum = True
End If
End Function