Excel VBA - TextBox Error

[bforster1]

I have a created a financial model which includes TextBoxes for the use
to input certain assumptions. If the user does not input a number i
TextBoxes, which are inserted in the spreadsheet, the program creates
error. For example, if the user enters ".50" it works fine but if th
user enters "..50" by accident it causes a debug error. How can
protect from this happening?

Thank

 

[papou]

Hi
You may use the IsNumeric function together with the AfterUpdate event of
your textbox:
Private Sub TextBox1_AfterUpdate()
If TextBox1 <> "" And Not IsNumeric(TextBox1) Then
MsgBox "Number expected here!" & vbLf & "please try again"
TextBox1.Text = ""
End If
End Sub

HTH
Cordially
Pascal

 

[Bob Phillips]

Dim cDots As Long

Private Sub txtRank_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If cDots > 1 Then
MsgBox "Too manuy dots"
Cancel = True
End If
End Sub

Private Sub txtRank_Change()
cDots = Len(txtRank.Text) - Len(Replace(txtRank.Text, ".", ""))
End Sub

Private Sub txtRank_Enter()
cDots = 0
End Sub

'---------------------------------------------------------------------------
Private Sub txtRank_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'---------------------------------------------------------------------------

Select Case KeyAscii
Case Asc("0") To Asc("9"): 'OK
Case Asc("."): cDots = cDots + 1
Case Else: KeyAscii = 0
End Select
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)