I have written the following code to ensure no formula error occurs i
the enduser enters something other than a numeric (alpha, ..., etc.)
want the MsgBox to fire only if a nonnumeric is entered. However, wit
the code below each time I hit tab or enter the MsgBox fires. Th
TextBox can either be populated or blank when the enduser loads th
UserForm. If the enduser enters a numeric I want the cell to populat
accordingly and move to the next textbox in the tab order. If
nonnumeric is entered I want the Msg to fire and when the enduser hit
OK the corresponding textbox (TextBox40 in the case below) should clea
and the focus should remain on TextBox40.

Private Sub TextBox40_AfterUpdate()
If TextBox40 < "" And Not IsNumeric(TextBox40) Then
MsgBox "Number Expected Here" & vbLf & "Please Try Again"
TextBox40.Text = ""
End If
Sheets("IncStmtAssump").Range("F7") = TextBox40.Value
If ComboBox6.Value = "% of Revenue" Then TextBox40.Text
Format(TextBox40.Text, "0.00%")
If ComboBox6.Value = "Input" Then TextBox40.Text
Format(TextBox40.Text, "$#,##0")
If ComboBox6.Value = "% Change from Previous Year" Then TextBox40.Tex
= Format(TextBox40.Text, "0.00%")
If ComboBox6.Value = "$ Change from Previous Year" Then TextBox40.Tex
= Format(TextBox40.Text, "$#,##0")
UserForm_Initialize
End Sub

Thanks

--
bforster
-----------------------------------------------------------------------
bforster1's Profile: http://www.excelforum.com/member.php...fo&userid=1177
View this thread: http://www.excelforum.com/showthread.php?threadid=27589

Try this


Private Sub TextBox40_exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox40 < "" And Not IsNumeric(TextBox40) Then
MsgBox "Number Expected Here" & vbLf & "Please Try Again"
Cancel = True
With TextBox40
.SelStart = 0
.SelLength = Len(.Text)
End With
End If
End Sub

Neil

"bforster1" wrote in message
...

I have written the following code to ensure no formula error occurs if
the enduser enters something other than a numeric (alpha, ..., etc.) I
want the MsgBox to fire only if a nonnumeric is entered. However, with
the code below each time I hit tab or enter the MsgBox fires. The
TextBox can either be populated or blank when the enduser loads the
UserForm. If the enduser enters a numeric I want the cell to populate
accordingly and move to the next textbox in the tab order. If a
nonnumeric is entered I want the Msg to fire and when the enduser hits
OK the corresponding textbox (TextBox40 in the case below) should clear
and the focus should remain on TextBox40.

Private Sub TextBox40_AfterUpdate()
If TextBox40 < "" And Not IsNumeric(TextBox40) Then
MsgBox "Number Expected Here" & vbLf & "Please Try Again"
TextBox40.Text = ""
End If
Sheets("IncStmtAssump").Range("F7") = TextBox40.Value
If ComboBox6.Value = "% of Revenue" Then TextBox40.Text =
Format(TextBox40.Text, "0.00%")
If ComboBox6.Value = "Input" Then TextBox40.Text =
Format(TextBox40.Text, "$#,##0")
If ComboBox6.Value = "% Change from Previous Year" Then TextBox40.Text
= Format(TextBox40.Text, "0.00%")
If ComboBox6.Value = "$ Change from Previous Year" Then TextBox40.Text
= Format(TextBox40.Text, "$#,##0")
UserForm_Initialize
End Sub

Thanks.


--
bforster1
------------------------------------------------------------------------
bforster1's Profile:
http://www.excelforum.com/member.php...o&userid=11771
View this thread: http://www.excelforum.com/showthread...hreadid=275895