VBA UserForm TextBox Only Allow Numbers and Empty Text

Question

In my userform I want to MsgBox if TextBox not contain Numbers or empty. This is my code but in another case when the TextBox = "" Empty the MsgBox appear to me, so the issue with me is the empty TextBox.

Private Sub TB1_Change()
    If TypeName(Me.TB1) = "TextBox" Then
        With Me.ActiveControl
            L12.Caption = Val(TB1.Text) * Val(TB2.Text)
            If Not IsNumeric(.Value) And .Value <> vbNullString Then
                MsgBox "Sorry, only numbers allowed"
                .Value = vbNullString
            End If
        End With
    End If
End Sub

What does MsgBox "Sorry, only numbers allowed but you entered '" & .Value & "'." write in between ' ? — Pᴇʜ
– Pᴇʜ, Commented May 9, 2017 at 8:18
Replace your MsgBox code with the code I posted. Run your code and tell us what is in the MsgBox when your textbox is empty. — Pᴇʜ
– Pᴇʜ, Commented May 9, 2017 at 8:22
Shouldn't With Me.ActiveControl be With Me.TB1 - at the moment it is using the value of the ActiveControl rather than the value of the TextBox. — YowE3K
– YowE3K, Commented May 9, 2017 at 8:31
I think you might want to re-visit your design. A message box popping up whenever you hit an illegal key, will be annoying as frick. — Jakob Busk Sørensen
– Jakob Busk Sørensen, Commented May 9, 2017 at 8:31
@Sagy.K - You should still rethink your design - the TB1_Change code will be getting executed every time the user enters a character in the textbox (so 5 times if they type "hello") or deletes a character, and it is also getting called every time your code changes the value of the textbox (e.g. initialising it). — YowE3K
– YowE3K, Commented May 9, 2017 at 8:46

Variatus · Accepted Answer · 2017-05-09 08:39:40Z

2

Use the Key Press event for this purpose.

Private Sub TB1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Not IsNumeric(Chr(KeyAscii)) Then KeyAscii = 0
End Sub

This procedure will just ignore anything you enter if it isn't a number, but you can modify both the condition and the output. For example, you might allow a decimal point to be entered, or you might wish to show a message box - perhaps only on the second try.

answered May 9, 2017 at 8:39

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Zemens · Accepted Answer · 2017-05-09 09:13:49Z

You may use the AfterUpdate event handler instead of the Change event, might also want to use the Exit event and cancel the exit if the user enters an invalid value:

Option Explicit
Private Sub TB1_AfterUpdate()
    'Check whether the value is numeric or empty:
     If Not IsValNumeric(Me.TB1.Value) Then
         MsgBox "Sorry, only numbers allowed"
         Me.TB1.Value = vbNullString

     Else:
         'Do something...
         MsgBox val(TB1.Text) * val(TB2.Text)
     End If
End Sub
Private Sub TB1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    'Prevents the user from EXIT the TextBox if value is not numeric/empty
    Cancel = Not IsNumeric(Me.TB1.Value)
End Sub
Private Function IsValNumeric(val$) As Boolean
    Dim ret As Boolean
    'check for numeric value only and allow empty value as a zero value
    ret = IsNumeric(val) Or Len(val) = 0
    IsValNumeric = ret
End Function

Robert Mearns · Accepted Answer · 2017-05-09 09:33:33Z

You can wait until the user finishes their input and then test the field.

For usability, the message box should be replaced with a caption and an icon/picture like this "A number must be entered here."

These would be displayed next to the text box when the input is incorrect. Then hidden when the input is corrected. The submission of the form can be blocked until all the errors have been corrected.

This allows the user to enter the request data and then fix any input errors. This is better than stopping them every time they make a mistake.

The event is changed from Change to Exit.

Private Sub TB1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TypeName(Me.TB1) = "TextBox" Then
        With Me.ActiveControl
            L12.Caption = Val(TB1.Text) * Val(TB2.Text)
            If Not IsNumeric(.Value) Or .Value = vbNullString Then
                MsgBox "Sorry, only numbers allowed"
                .Value = vbNullString
            End If
        End With
    End If
End Sub

The vbNullString test has also been updated.

NNOPP · Accepted Answer · 2017-05-11 01:54:38Z

0

Since you are trying to allow only "Numeric" and "Blank" then the code below would deliver your needs.

Private Sub TB1_Change()
    if IsNumeric(Me.TB1.Value) = True or Me.TB1.Value = vbNullString then
        'Good data, nothing to MSG
    Else
        MsgBox "Your input data is not valid"
    Endif
End Sub

edited May 11, 2017 at 1:54

answered May 9, 2017 at 9:13

NNOPP

1071 silver badge11 bronze badges

1 Comment

J. Chomel Over a year ago

Your answer certainly is worth a little explanation. Kindly refer to stackoverflow.com/help/how-to-answer . Comments would help create searchable content.

Collectives™ on Stack Overflow

VBA UserForm TextBox Only Allow Numbers and Empty Text

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related