Validate textbox input

Question

I created a userform in Excel and have a range for data validation.

I want to check the user's input before the user clicks the "Save" button.

Ex: Value input is "x", if 1 <= x <= 10, the Data textbox will mark as red highlight and popup the message "Out of control", the form will request user continue input in "Re Test" text box within the data range (in Excel file).

Here is my code and attachment file also http://www.mediafire.com/view/fl1nk84nu62wg34/Control_Chart.xlsm

Private Sub cbCa_DropButtonClick()
    cbCa.AddItem ("Ca 1")
    cbCa.AddItem ("Ca 2")
    cbCa.AddItem ("Ca 3")
    cbCa.AddItem ("")
End Sub

Private Sub cbType_DropButtonClick()
    cbType.AddItem ("Set Up")
    cbType.AddItem ("Production")
End Sub

Private Sub CommandButton1_Click()
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet

    Set ws = Worksheets("Input Data")
    lRow = ws.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 2).Value = Me.txNgay.Value
        .Cells(lRow, 3).Value = Me.txGio.Value
        .Cells(lRow, 4).Value = Me.cbCa.Value
        .Cells(lRow, 5).Value = Me.txNV.Value
        .Cells(lRow, 6).Value = Me.txSolo.Value
        .Cells(lRow, 7).Value = Me.txMa.Value
        .Cells(lRow, 8).Value = Me.txData.Value
        .Cells(lRow, 9).Value = Me.txReTest.Value
        .Cells(lRow, 10).Value = Me.txLydo.Value
        .Cells(lRow, 11).Value = Me.cbType.Value
    End With

       'Hien thong bao nhac nho
If txNgay.Text = "" Then
        MsgBox "Quen cho ngay kia thim", vbOKOnly + vbInformation, 
                 "THÔNG BÁO"

    txNgay.BackColor = &HFF& ' change the color of the textbox

    ElseIf txGio.Text = "" Then
        MsgBox "Quen nhap gio kia thim", vbOKOnly + vbInformation, 
                  "THÔNG BÁO"

        txGio.BackColor = &HFF& ' change the color of the textbox

    ElseIf txNV.Text = "" Then
        MsgBox "Ten cua thim la gi vay", vbOKOnly + vbInformation, 
                 "THÔNG BÁO"

        txNV.BackColor = &HFF& ' change the color of the textbox

    ElseIf txMa.Text = "" Then
        MsgBox "Quen nhap Ma san pham kia thim", vbOKOnly + vbInformation, 
                  "THÔNG BÁO"

        txMa.BackColor = &HFF& ' change the color of the textbox

    ElseIf txSolo.Text = "" Then
        MsgBox "Quen nhap So lo kia thim", vbOKOnly + vbInformation, "THÔNG 
                  BÁO"

        txSolo.BackColor = &HFF& ' change the color of the textbox

    ElseIf txData.Text = "" Then
        MsgBox "Quen nhap data kia thim", vbOKOnly + vbInformation, "THÔNG 
                  BÁO"

        txData.BackColor = &HFF& ' change the color of the textbox

    End If

    ThisWorkbook.Save

    '    End If

End Sub

Private Sub CommandButton2_Click()
 'Clear input controls.
    Me.txNgay.Value = ""
    Me.txGio.Value = ""
    Me.cbCa.Value = ""
    Me.txNV.Value = ""
    Me.txSolo.Value = ""
    Me.txMa.Value = ""
    Me.txData.Value = ""
    Me.txLydo.Value = ""

End Sub

Private Sub CommandButton3_Click()
  'Close UserForm.
    Unload Me
End Sub

Private Sub CommandButton4_Click()
ThisWorkbook.Sheets("Control_Chart").Visible = True
ThisWorkbook.Sheets("Control_Chart").Select
UserForm1.Hide
End Sub

Private Sub CommandButton5_Click()
Calendar1.Visible = True
End Sub

Private Sub Label15_Click()
Label15.Caption = Sheet2.Range("F2").Value
End Sub

Private Sub Label16_Click()
Label16.Caption = Sheet2.Range("F4").Value
End Sub

Private Sub Label17_Click()
Label17.Caption = Sheet2.Range("F3").Value
End Sub

Private Sub Label18_Click()
Label18.Caption = Sheet2.Range("F6").Value
End Sub

Private Sub Label20_Click()
Label20.Caption = Sheet2.Range("F5").Value
End Sub

Private Sub Label23_Click()

End Sub

Private Sub Label8_Click()
Range("F2").Select
End Sub

Public iDate As Long
Private Sub Calendar1_Click()
  iDate = Calendar1.Value
  txNgay.Value = Format(iDate, "dd/mm/yyyy")
  Calendar1.Visible = False
End Sub

Private Sub txData_Enter()
With Me.txData
        If .Value >= 0.315 Or .Value <= 0.33 Then
            .Value = ""
            MsgBox prompt:="Must be a # between 1 and 30000!", 
                Buttons:=vbCritical, Title:="Invalid Entry"
            Cancel = True
       End If
    End With
End Sub

Private Sub txData_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 If InStr("1234567890." + Chr$(vbKeyBack), Chr$(KeyAscii)) = 0 Then
  KeyAscii = 0
 End If
End Sub

Private Sub txMa_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 If InStr("1234567890." + Chr$(vbKeyBack), Chr$(KeyAscii)) = 0 Then
  KeyAscii = 0
 End If
End Sub


Private Sub UserForm_Initialize()
  Calendar1.Visible = False
    Label15.Visible = True
    Label16.Visible = True
    Label17.Visible = True
    Label18.Visible = True
    Label20.Visible = True
End Sub

Download the attachment file

Here is my updated the code

'Khai bao bien Data khi nguoi dung nhap lieu
Private Sub txData_AfterUpdate()
Dim x As Double 'Bien do so nhi phan
Dim F2 As Double
Dim F3 As Double
Dim F4 As Double
Dim F5 As Double
Dim F6 As Double

x = Me.txData.Value ' Set gia tri cho bien
F2 = Sheet2.Range("F2").Value
F3 = Sheet2.Range("F3").Value
F4 = Sheet2.Range("F4").Value
F5 = Sheet2.Range("F5").Value
F6 = Sheet2.Range("F6").Value

'Kiem tra hop Data Input co trong hay khong
    If Me.txData = Empty Then
        MsgBox "Please input your data", vbCritical
        txData.BackColor = &HFF& ' change the color of the textbox

        ' Kiem tra hop Data Input co phai la so hay khong
        ElseIf Not IsNumeric(Me.txData) Then
        MsgBox "Numberic only", vbCritical
        txData.BackColor = &HFF& ' change the color of the textbox
        Else
        'Xet dieu kien nguoi dung nhap vao
        Select Case x
            Case x = 0
                MsgBox "Data sao bang 0 duoc", vbCritical
                txData.BackColor = &HFF& ' change the color of the textbox
                txData.SetFocus
             Case Is < F6
                MsgBox "Lower Out of Control. Ban hay nhap gia tri vao o Re Test", vbCritical
                txData.BackColor = &HFF& ' change the color of the textbox
                txReTest.SetFocus
             Case Is > F4
                MsgBox "Upper Out of Control. Ban hay nhap gia tri vao o Re Test", vbCritical
                txData.BackColor = &HFF& ' change the color of the textbox
                txReTest.SetFocus
             Case F5
                MsgBox "Database is correct", vbInformation
                End Select
        ThisWorkbook.Save
    End If

End Sub

SilentRevolution · Accepted Answer · 2016-01-17 17:09:57Z

0

Since you're setting x to the value of the textbox, you don't need the if statements in the case select.

Select Case x
    Case 0.3
        MsgBox "Correct"
    Case Is < 0.3
        MsgBox "Lower limit"
    Case Is > 0.3
        MsgBox "Upper Limit"
End Select

Note that the limits I've used do not necessarily correspond to you're limits.

To validate a textbox, you could write an afterupdate sub which triggers when you deselect a textbox by pressing the tab key for example. It might look like something like the sub below, be aware that you either to write a sub for each input you want to check, if you have a lot of similar inputs you could possibly write a private sub which is called from the afterupdate sub which checks if the value of the textbox is numeric, and lies within certain paramaters.

Private Sub txNgay_AfterUpdate()
    If Me.txNgay = Empty Then
        'Code which sets the the look of textbox back to normal.
    ElseIf Not IsNumeric(Me.txNgay) Then
        MsgBox "The textbox doesn't contain a numerical value.", vbCritical
        'Code which sets the look of the textbox to a faulty state
    ElseIf Me.txNgay < 1 Or Me.txNgay > 10 Then
        MsgBox "The value is out of range"
        'Code which sets the look of the textbox to a faulty state
    Else
        'Code which sets the look of the textbox to a normal state
    End If
End Sub

You might could even include a private sub in the module which is called every time a field is updated which checks all inputs and only activates the OK button when all conditions are satisfied.

you can use case select to execute code based on values

Select Case x
    Case 0
        'Code to perform the actions you need if x is 0
    Case 1 To 10
        'Code to perform the actions you need if x is between 1 and 10
    Case Else
        'Code to perform the actions you need if x is < 0 and x > 10
End Select

edited Jan 17, 2016 at 17:09

answered Jan 17, 2016 at 0:01

SilentRevolution

1,5132 gold badges16 silver badges31 bronze badges

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

7 Comments

Diep Long Over a year ago

Appreciate for your response and help me SilentRevolution. I will combine each textbox to event AfterUpdate. For the Data textbox validation, the condition for checking is 1<x<10 equal to "Out of control". But when user input 0 or any number is also trigger the Msgbox message. Please help me...

SilentRevolution Over a year ago

you could do something with Case Select, I'll add it to the answer.

SilentRevolution Over a year ago

with txData.value = x you're setting the value of the textbox to the value of x, to set the value of x variable, you need x = txData.value. The integer x has a default value of 0 when running the program starts since the variable is not actually changed, Case 0 is always selected.

Diep Long Over a year ago

Thanks for your recommendation @SilentRevolution. I follow your guide and used "Select Case". I called "Dim x as Integer" and provided "txData.Value = x" to set the variable. Then in the "Select Case" and compared the value when user input in txData. Now the code had been change. Any value I inputted alway popup the MsgBox "Upper Out of control" but for the value 0 (zero) is fine. Please spend a little to review my code in the question area.

Diep Long Over a year ago

Ya, I changed "Dim x as Double" and also edit "x = txData.Value". Now in the "Select Case" it run smoothly. Appreciate so much, SilentRevolution, you are my teacher in vba.

|

Collectives™ on Stack Overflow

Validate textbox input

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related