BeforeUpdate event validation control

Question

Dears,

I want to make a simple userform to record some serial numbers into excel, it contains a textbox_serialNo., a command button “enter” and another command button “cancel”.

I made a validation control in that serialNo textbox so that only number can be entered. However, when I run the program and input some numbers into the textbox, both command buttons (the "enter" button named as label_enter,the "cancel" button named as label_cancel) have no reactions (e.g. the "cancel" button doesn't unload the form when press) , how should I correct the program? Below are the relevant codes, Thanks.

Private Sub TextBox_SerialNo_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(TextBox_SerialNo.Value) Then
    TextBox_SerialNo.BackColor = rgbYellow
End If
Cancel = True
End Sub

Private Sub TextBox_SerialNo_AfterUpdate()
If TextBox_SerialNo.Value <> "" Then
    TextBox_SerialNo.BackColor = rgbWhite
End If
End Sub

Private sub label_enter_click()
sheet1.Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
ActiveCell.Offset(0, 1) = TextBox_SerialNo.Value
 TextBox_SerialNo.Value = ""
 End Sub

Private Sub Label_Cancel_Click()
Unload Me
End Sub

Answer 1

Sorry to be posting as an answer, not enough rep.

Shouldn't Cancel=True be inside the if statement? You are locking it up regardless of entry being numeric or not as is.

Edit: Actually upon further testing still not working proper. However, change event works better and you can get instant feedback for any non numerics.

Updated code would look like this, control names differ. I am used to working with .Text, same thing as .Value. Also, since I am not sure what you would do with an empty string, assumed it to be yellow background as well.

One concern would be, can you allow comma or period in there? Depending on locale settings, a decimal would also be considered a numeric.

Private Sub cmdCancel_Click()

    Unload Me

End Sub

Private Sub cmdEnter_Click()
    
    If TextBox1.BackColor = rgbYellow Then Exit Sub
    test4.Range("A1").Value = TextBox1.Text

End Sub

Private Sub TextBox1_Change()

    If Not IsNumeric(TextBox1.Text) Or TextBox1.Text = "" Then
        TextBox1.BackColor = rgbYellow
    Else
        If TextBox1.Text <> "" Then
            TextBox1.BackColor = rgbWhite
        End If
    End If
    
End Sub

Edit 2: I use this piece of code to check for only numbers (assuming number Ascii codes are standard). Maybe it can help.

Public Function isnumber(ByVal strValue As Variant) As Boolean

    On Error Resume Next
    
    Dim i As Long
    
    isnumber = True
    If Not strValue = "" Then
        For i = 1 To Len(CStr(strValue))
            If Asc(Mid(strValue, i, 1)) > 57 Or Asc(Mid(strValue, i, 1)) < 48 Then
                isnumber = False
                Exit For
            End If
        Next i
    Else
        isnumber = False
    End If
    
    On Error GoTo 0
    Err.Clear
    
End Function

Edit 3: I have revised the TextBox1_Change event code so all invalid characters are stripped right away. However, in this state if you copy paste a serial no with a non-allowed char, it will strip them leaving only the numbers. Not sure if it is acceptable.

Private Sub TextBox1_Change()

    If Not isnumber(TextBox1.Text) Or TextBox1.Text = "" Then
        TextBox1.BackColor = rgbYellow
            
        Dim i As Long
        Dim strValue As String
        
        strValue = ""
        
        If Not TextBox1.Text = "" Then
            For i = 1 To Len(CStr(TextBox1.Text))
                If Not (Asc(Mid(TextBox1.Text, i, 1)) > 57 Or Asc(Mid(TextBox1.Text, i, 1)) < 48) Then
                    strValue = strValue & Mid(TextBox1.Text, i, 1)
                End If
            Next i
        End If
        
        TextBox1.Text = strValue
        
    Else
        If TextBox1.Text <> "" Then
            TextBox1.BackColor = rgbWhite
        End If
    End If
    
End Sub