CODEWITHSUNDEEP
functionvba
Daniel Faria
Daniel Faria

Reputation: 13

VBA isnumber istext function problem

im trying to check if a number is a text or number in a banking system. If the value is incorrect it should erase and send an error message, but my code isnt working at all... can someone help me? here is the code:

Private Sub TextBox1_Change()

   name = TextBox1

   If Application.IsText(name) = True Then
      IsText = True
   Else
      MsgBox "Insert only words"
      Selection.name.Delete
   End If

End Sub

Upvotes: 0

Views: 7785

Answers (2)

ragoertz
ragoertz

Reputation: 13

Have you considered the vartype() function? https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vartype-function

Upvotes: 0

jonsca
jonsca

Reputation: 10381

I think you should be using name = TextBox1.Text instead. It's probably giving you an error on that line.

Also, you may want to wait until the user is finished typing the response, I believe the changed event will run each time a character is pressed. The AfterUpdate will run after you tab or click away from the textbox.

And really, instead of deleting the response (which I don't think will work), you should simply set the textbox blank back to an empty string.

Private Sub TextBox1_AfterUpdate()
    If Not MyIsANumber(TextBox1.Text) Then
        MsgBox ("It's Text")
    Else
        TextBox1.Text = ""
    End If
End Sub

Function MyIsANumber(str As String) As Boolean
    For i = 1 To Len(str)
        If IsNumeric(Mid(str, i, 1)) Then
           MyIsANumber = True
           Exit Function
        End If
    Next
        MyIsANumber = False
 End Function

I'm sure the code could be structured better, but there's something along the lines of what you need. In this case, it just wipes the textbox if there is a number entered. Obviously, things will need to be the opposite for your textbox that you want to contain a number.

Upvotes: 1

Related Questions