验证用户窗体文本框条目的格式 [英] Validating the format of a userform textbox entry

问题描述

我有一个正在制作的数据库数据提取器的用户窗体.在其中有一个文本框，用于输入用户要为其提取数据的零件号.我想验证用户是否在批量提取器运行之前输入了正确的零件号格式.为此，我需要一个代码来验证是否以特定格式输入了文本:

I have a UserForm for a database data extractor I'm making. In it there is a TextBox for typing in the part number that the user wants to extract data for. I want to validate that the user has entered the correct format of part number before the bulk of the extractor runs. To do this I need a code to validate that the text is entered in the specific format:

3个数字字符1个字母字符或1个Hyphon然后是5个数字字符

3 Numeric Characters 1 Alphabetic Character or 1 Hyphon then 5 Numeric Characters

我首先尝试了以下验证:

I tried the following validations at first:

'validate that box is not empty 

If TextBox1.Value = "" Then 

MsgBox ("Sorry, you need to provide an Amount") 

TextBox1.SetFocus 

Exit Sub 

End If 


'validate that box is numeric 

If Not IsNumeric(TextBox1.Value) Then 

MsgBox ("Sorry, must enter a numer") 

TextBox1.SetFocus 

Exit Sub 

End If 

但是后来我意识到我遇到的问题是，在第四位置可能有字母字符或连音.

But then I realised that I had the problem that there may be an alphabetic char or hyphon in the fourth position.

任何建议，我将不胜感激.

I would appreciate any suggestions.

谢谢.

推荐答案

检查此输入的新手方法是将输入字符串切碎并根据需要比较各个部分:

A beginner way to check this input is to just chop up the input string and compare the parts as needed:

Const alpha as String = "abcdefghijklmnopqrstuvwxyz-"
Dim strValue as String
Dim msg as String
strValue = TextBox1.Value

'Make sure it's the right LENGTH
If Len(strValue) <> 9 Then 
    msg = "Please enter the ID as 3 numeric, 1 alpha/hyphen, 5 numeric"
    GoTo EarlyExit
End If

'Check the first three for numeric:
If Not IsNumeric(Left(strValue), 3) Then
    msg = "The first three characters should be numeric"
    GoTo EarlyExit
End If

'Check the middle character, assuming case not sensitive:
If Instr(1, alpha, Lcase(Mid(strValue, 4, 1)) = 0 Then 
    msg = "The fourth character should be hyphen or alphabet"
    GoTo EarlyExit
End If

'Check the last four characters
If Not IsNumeric(Right(strValue, 4)) Then
    msg = "The last four characters should be numeric"
    GoTo EarlyExit
End If

'If you've gotten here, then the input is validated:
Exit Sub 

EarlyExit:
MsgBox msg
TextBox1.SetFocus
End Sub

3个数字字符1个字母字符或1个连字符，然后5个数字

3 Numeric Characters 1 Alphabetic Character or 1 Hyphon then 5 Numeric Characters

这篇关于验证用户窗体文本框条目的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！