[已解决]怎么样对单元格输入内容进行有效性验证

Lydia_Li

求助贴 ，相对单元格输入内容进行有效性验证，请看附件~谢谢各位

Sub Button1_Click()
       Dim Entry1 As Variant, Entry2 As Variant
       Dim Msg1 As Variant, Msg2 As Variant
       Dim NextRow As Variant
       NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
       Msg1 = "Enter the name"
       Msg2 = "Enter the amount"
  Do
       Entry1 = InputBox(Msg1)
       Entry2 = InputBox(Msg2)
       If Entry1 = "" Then Exit Sub
       If Entry2 = "" Then Exit Sub
       If IsNumeric(Entry1) Then
             Msg1 = "Your previous is INVALID"
             Msg1 = Msg1 & vbNewLine
             Msg1 = Msg1 & "Enter the name"
       End If
       If Not IsNumeric(Entry2) Then
             Msg2 = "Your previous is INVALID"
             Msg2 = Msg2 & vbNewLine
             Msg2 = Msg2 & "Enter the amount"
       End If
            Cells(NextRow, 1) = Entry1
            Cells(NextRow, 2) = Entry2
    Loop
End Sub

最佳答案

月排行榜 / 总排行榜

wenzili

2017-3-22 14:43

Lydia_Li 发表于 2017-3-22 13:47
非常感谢老师的耐心解答。我才开始学习VBA，您写的程序暂时我还有些接受困难，

能否请老师再修改下程 ...

其实不难懂，我注释一下
Sub Button1_Click()
        Dim Entry1 As Variant, Entry2 As Variant
        Dim Msg1 As Variant, Msg2 As Variant
        Dim NextRow As Variant
        Dim reg, s$
        
      CreateObject ("vbscript.regexp")
      Set reg = CreateObject("vbscript.regexp")
      reg.Pattern = "^[A-Za-z]+$"        ‘上面三句为正则，是为了判断输入值是否全部为字母
        Msg1 = "Enter the name"
        Msg2 = "Enter the amount"
   
   Do
   NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
     Do ‘这个do语句一直运行，要求输入，直到reg.Test(Entry1)值为true
        Entry1 = Application.InputBox(prompt:=Msg1, Type:=2) ’不用原来的函数，用inputbox方法，输入框点取消时entry值为false
        If Entry1 = False Then Exit Sub ' 点取消退出程序
        
     Loop Until reg.Test(Entry1) ’ 判断输入值是否全部为字母，不是的话，再次运行要求输入。
        Entry2 = Application.InputBox(prompt:=Msg2, Type:=1) ’inputbox方法，type=1为只接受数字输入，不是数字会要求重输。
        
        If Entry2 = False Then Exit Sub ‘点取消退出程序。

             Cells(NextRow, 1) = Entry1
             Cells(NextRow, 2) = Entry2
     Loop
End Sub
主要问题是判断entry1输入的全部是字母，没有别的办法，只有用正则，我也不懂，抄的爱疯的帖子中的语句。

跳转到最佳答案楼层

wenzili

Sub Button1_Click()
       Dim Entry1 As Variant, Entry2 As Variant
       Dim Msg1 As Variant, Msg2 As Variant
       Dim NextRow As Variant
       Dim reg, s$
      
     CreateObject ("vbscript.regexp")
     Set reg = CreateObject("vbscript.regexp")
     reg.Pattern = "^[A-Za-z]+$"
       NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
       Msg1 = "Enter the name"
       Msg2 = "Enter the amount"
  
  Do
    Do
       Entry1 = Application.InputBox(prompt:=Msg1, Type:=2)
       If Entry1 = False Then Exit Sub
      
    Loop Until reg.Test(Entry1)
       Entry2 = Application.InputBox(prompt:=Msg2, Type:=1)
       If Entry1 = "" Then Exit Sub
       If Entry2 = "" Then Exit Sub
       If IsNumeric(Entry1) Then
             Msg1 = "Your previous is INVALID"
             Msg1 = Msg1 & vbNewLine
             Msg1 = Msg1 & "Enter the name"
       End If
       If Not IsNumeric(Entry2) Then
             Msg2 = "Your previous is INVALID"
             Msg2 = Msg2 & vbNewLine
             Msg2 = Msg2 & "Enter the amount"
       End If
            Cells(NextRow, 1) = Entry1
            Cells(NextRow, 2) = Entry2
    Loop
End Sub

wenzili

Sub Button1_Click()
       Dim Entry1 As Variant, Entry2 As Variant
       Dim Msg1 As Variant, Msg2 As Variant
       Dim NextRow As Variant
       Dim reg, s$
      
     CreateObject ("vbscript.regexp")
     Set reg = CreateObject("vbscript.regexp")
     reg.Pattern = "^[A-Za-z]+$"
       NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
       Msg1 = "Enter the name"
       Msg2 = "Enter the amount"
  
  Do
    Do
       Entry1 = Application.InputBox(prompt:=Msg1, Type:=2)
       If Entry1 = False Then Exit Sub
      
    Loop Until reg.Test(Entry1)
       Entry2 = Application.InputBox(prompt:=Msg2, Type:=1)
      
       If Entry2 = False Then Exit Sub

            Cells(NextRow, 1) = Entry1
            Cells(NextRow, 2) = Entry2
    Loop
End Sub

优化版

wenzili

Sub Button1_Click()
       Dim Entry1 As Variant, Entry2 As Variant
       Dim Msg1 As Variant, Msg2 As Variant
       Dim NextRow As Variant
       Dim reg, s$
      
     CreateObject ("vbscript.regexp")
     Set reg = CreateObject("vbscript.regexp")
     reg.Pattern = "^[A-Za-z]+$"
       Msg1 = "Enter the name"
       Msg2 = "Enter the amount"
  
  Do
  NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Do
       Entry1 = Application.InputBox(prompt:=Msg1, Type:=2)
       If Entry1 = False Then Exit Sub
      
    Loop Until reg.Test(Entry1)
       Entry2 = Application.InputBox(prompt:=Msg2, Type:=1)
      
       If Entry2 = False Then Exit Sub

            Cells(NextRow, 1) = Entry1
            Cells(NextRow, 2) = Entry2
    Loop
End Sub
修改一下

Lydia_Li

wenzili 发表于 2017-3-21 16:20
Sub Button1_Click()
       Dim Entry1 As Variant, Entry2 As Variant
       Dim Msg1 As Variant, Ms ...

非常感谢老师的耐心解答。我才开始学习VBA，您写的程序暂时我还有些接受困难，

能否请老师再修改下程序，用if-then 结构，验证性函数IsNumeric等，这种简单的语句比较适合初学者，谢谢您~

wenzili

Lydia_Li 发表于 2017-3-22 13:47
非常感谢老师的耐心解答。我才开始学习VBA，您写的程序暂时我还有些接受困难，

能否请老师再修改下程 ...

其实不难懂，我注释一下
Sub Button1_Click()
        Dim Entry1 As Variant, Entry2 As Variant
        Dim Msg1 As Variant, Msg2 As Variant
        Dim NextRow As Variant
        Dim reg, s$
        
      CreateObject ("vbscript.regexp")
      Set reg = CreateObject("vbscript.regexp")
      reg.Pattern = "^[A-Za-z]+$"        ‘上面三句为正则，是为了判断输入值是否全部为字母
        Msg1 = "Enter the name"
        Msg2 = "Enter the amount"
   
   Do
   NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
     Do ‘这个do语句一直运行，要求输入，直到reg.Test(Entry1)值为true
        Entry1 = Application.InputBox(prompt:=Msg1, Type:=2) ’不用原来的函数，用inputbox方法，输入框点取消时entry值为false
        If Entry1 = False Then Exit Sub ' 点取消退出程序
        
     Loop Until reg.Test(Entry1) ’ 判断输入值是否全部为字母，不是的话，再次运行要求输入。
        Entry2 = Application.InputBox(prompt:=Msg2, Type:=1) ’inputbox方法，type=1为只接受数字输入，不是数字会要求重输。
        
        If Entry2 = False Then Exit Sub ‘点取消退出程序。

             Cells(NextRow, 1) = Entry1
             Cells(NextRow, 2) = Entry2
     Loop
End Sub
主要问题是判断entry1输入的全部是字母，没有别的办法，只有用正则，我也不懂，抄的爱疯的帖子中的语句。