Validating Textbox Entry As Numeric

[f64]

In a Subroutine, I am validating that a textbox entry is numeric. If a non-numeric is entered, a message is displayed, and when OK is clicked I would like to re-enter the value in the textbox, after deleting the rightmost non-numeric character.

Dim Entry As Object
------------------------------------
Private Sub TextBox_Change()

Call Check_Format(TextBox)

End Sub
-------------------------------------------------
Private Sub Check_Format(Entry)

If Not IsNumeric(Entry) Then
MsgBox " Sorry, Numeric Entries Only!"
** Entry.Text = Application.WorksheetFunction.Left _
(Entry.Text, Application.WorksheetFunction.Len _(Entry.Text) – 1))
End If

End Sub

** This statement generates the following
Run-Time error ‘438’
Object doesn’t support this property or method

Is there a method to re-enter the value, after deleting the last character in the string?

 

[fumei]

Just use Left?

Left(entry, Len(entry) - 1)

But what if not just the last charcter is non-numeric? Are you really going to loop back through all the characters?

Gerry

 

[rmikesmith]

f64,

Try this modified code:

Dim EventActive As Boolean


Private Sub TextBox_Change()
   If EventActive Then Exit Sub
   Call Check_Format(TextBox)
End Sub

Private Sub Check_Format(ByRef Entry As MSForms.TextBox)
  
   If Not IsNumeric(Entry.Text) Then
     MsgBox " Sorry,  Numeric  Entries  Only!"
     EventActive = True
     Entry.Text = Left$(Entry.Text, Len(Entry.Text) - 1)
     EventActive = False
   End If
 End Sub

Regards,
Mike