验证文本框输入 [英] Validate textbox input
问题描述
我在Excel中创建了一个用户表单，并有一个范围用于数据验证.
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:值输入为"x"，如果 1< = x< = 10 ，则数据"文本框将标记为红色突出显示，并弹出消息失控"，形式为将要求用户继续在数据范围(在Excel文件中)的重新测试"文本框中输入内容.
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).
这也是我的代码和附件文件 http://www.mediafire.com/view/fl1nk84nu62wg34/Control_Chart.xlsm
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
这是我更新的代码
'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
推荐答案
由于将x设置为文本框的值，因此在选择case时不需要if语句.
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.
要验证文本框，您可以编写一个afterupdate子例程，该子例程在您通过按Tab键取消选择文本框时触发.它可能看起来像下面的sub，请注意，您可以为要检查的每个输入编写一个sub，如果您有很多类似的输入，则可以编写一个从afterupdate sub调用的私有sub.检查文本框的值是否为数字，并且是否位于某些参数内.
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
这篇关于验证文本框输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
