EXCEL validate userform textbox

Question

I have a userform with checkbox and textboxes. I want to achieve make validation so that user can insert numbers only or leave blank. If left blank, then back color of the textbox becomes white, if the value is not a number, then back color is red. I have the following code, but it works wrong, because:

when i move to another textbox, text remains red.

when i insert intentionally a wrong value, i can not move to another textbox until i put the correct value in it. While it should become white again if i just move to another textbox leaving it blank.

 Private Sub txt_1x2_1_from_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If Not IsNumeric(txt_1x2_1_from.Value) Then
         txt_1x2_1_from.BackColor = vbRed
         txt_1x2_1_from.Text = ""
     ElseIf (txt_1x2_1_from.Value = "") Then
         txt_1x2_1_from.BackColor = vbWhite
     Else
         txt_1x2_1_from.BackColor = vbWhite
         Cancel = True

     End If
 End Sub

There's no question or problem mentioned? Something works wrong... care to elaborate? Guessing if you enter an invalid number then try to correct it the Cancel = True prevents it, simple take that line out. — Jeremy Thompson
– Jeremy Thompson, Commented Jan 2, 2021 at 9:29
Yes, sure. Sorry if I was not clear enough. Now I have edited question. — Zaur
– Zaur, Commented Jan 2, 2021 at 9:52
when i move to another textbox, text remains red. There is never left text in txt_1x2_1_from when you move to another textbox. Are you talking about the background color of the textbox? — Storax
– Storax, Commented Jan 2, 2021 at 9:54

Storax · Accepted Answer · 2021-01-02 10:23:51Z

1

It seems you also got your logic wrong as I think you only want the background color of the textbox red if the input is not a number and not blank.

Private Sub txt_1x2_1_from_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsNumeric(txt_1x2_1_from.Value) Then
 
        If (txt_1x2_1_from.Value = "") Then
            txt_1x2_1_from.BackColor = vbWhite
        Else
            txt_1x2_1_from.BackColor = vbRed
            txt_1x2_1_from.Text = ""
        End If

    Else
    
        txt_1x2_1_from.BackColor = vbWhite
        
    End If
End Sub

Update: It seems that the OP wants something different. One needs to use the keypress event.

Private Sub txt_1x2_1_from_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Debug.Print KeyAscii
    If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 44 Then
        txt_1x2_1_from.BackColor = vbWhite
    Else
        KeyAscii = 0
        txt_1x2_1_from.BackColor = vbRed
    End If
End Sub

This code still might need some improvement in order to check for all allowed keys.

edited Jan 2, 2021 at 10:23

answered Jan 2, 2021 at 9:50

Storax

12.7k3 gold badges21 silver badges36 bronze badges

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

6 Comments

Zaur Over a year ago

Yes, like this, but if i move to another textbox and leave this one blank, then its background color should become white again.

Storax Over a year ago

The background color only changes to red after you have entered text and leave the textbox. And the text will be emptied whenever it is not numeric. So, the code you showed never did what you requested.

Zaur Over a year ago

Thank you! How do i make it accept a decimal numbers also?

Storax Over a year ago

Do you enter a point or a comma as decimal separator?

Zaur Over a year ago

i have an italian locale. It is point. But it should work for both point and comma, depending who will open a file.

|

Darren Bartrup-Cook · Accepted Answer · 2021-01-02 12:41:38Z

Another method would be to not allow text to be entered in the first place. In the example below I've added the case-sensitive word 'Numerical' to the controls Tag property for text boxes that must be numeric.

Rather than not allowing the user to move on if an incorrect entry is added I've always found it easier to check the whole form when a Save button is pressed.
I've added code at the bottom for changing the back colour of the control.

Create a Class and name it clsTxtBxEvent.

Option Explicit

Public WithEvents txtBox As MSForms.TextBox

Private Sub txtBox_Change()

    Static LastText As String
    Static SecondTime As Boolean
    Const MaxDecimal As Integer = 2
    
    With txtBox
  
        'Allow only numbers with <=2 decimal places
        If Not SecondTime Then
            If .Text Like "[!0-9.-]*" Or Val(.Text) < -1 Or _
                .Text Like "*.*.*" Or .Text Like "*." & String$(1 + MaxDecimal, "#") Or _
                .Text Like "?*[!0-9.]*" Then
                SecondTime = True
                .Text = LastText
            Else
                LastText = .Text
            End If
        End If
        SecondTime = False
    
    '    'Allow only whole numbers.
    '    If .Text Like "[!0-9]" Or Val(.Text) < -1 Or .Text Like "?*[!0-9]*" Then
    '        .Text = LastText
    '    Else
    '        LastText = .Text
    '    End If

    End With
                
End Sub

Add this to your form:

Option Explicit

Private TextBoxCollection As Collection

Private Sub UserForm_Initialize()
    
    Dim TxtBxEvent As clsTxtBxEvent
    Dim ctrl As Control
    
    Set TextBoxCollection = New Collection
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
            Case "TextBox"
                If ctrl.Tag = "Numeric" Then '<Remove check if all textboxes.
                    Set TxtBxEvent = New clsTxtBxEvent
                    Set TxtBxEvent.txtBox = ctrl
                    TextBoxCollection.Add TxtBxEvent
                End If '<Remember to remove this as well
        End Select
    Next ctrl

End Sub

This code added to the form will change the back colour of the control.

Private Sub ColourBackground(ctrl As Control, Optional ColourOn As Boolean = True)
    If ColourOn Then
        ctrl.BackColor = &HFFC0C0
    Else
        ctrl.BackColor = &H80000005
    End If
End Sub

'Colour controls on Enter / Exit                                          '
'Enter / Exit isn't exposed to class objects, so needs coding separately. '
Private Sub TextBox1_Enter()
    ColourBackground Me.TextBox1
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ColourBackground Me.TextBox1, False
End Sub

Private Sub TextBox2_Enter()
    ColourBackground Me.TextBox2
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ColourBackground Me.TextBox2, False
End Sub

I haven't tried, but you could move the ColourBackground procedure to the class module and check against the controls Tag value for max/min allowed values in specific controls and colour accordingly on update.

Bit of a disclaimer - I had a similar question years ago and was answered on MrExcel.

Collectives™ on Stack Overflow

EXCEL validate userform textbox

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related